﻿/**************************************************************
* Copyright (C) 2022 www.eipflow.com 孙泽伟版权所有(盗版必究)
*
* 作者: 孙泽伟(QQ 1039318332)
* 创建时间: 2022/01/12 22:40:15
* 文件名: 
* 描述: 
* 
* 修改历史
* 修改人：
* 时间：
* 修改说明：
*
**************************************************************/
using MySqlConnection = MySqlConnector.MySqlConnection;

namespace EIP.System.Logic.Impl
{
    /// <summary>
    /// 系统配置文件接口实现
    /// </summary>
    public class SystemWorkSheetLogic : ISystemWorkSheetLogic
    {
        #region 构造函数

        /// <summary>
        /// filterSql字段，是否过滤数据字段
        /// </summary>
        private readonly string[] _filterSqlField = { "switch", "sign", "editor" };

        /// <summary>
        /// 数据库连接
        /// </summary>
        private readonly SystemDataBaseInput dataBase = new SystemDataBaseInput();

        private readonly ISystemDataBaseRepository _dataBaseRepository;
        private readonly ISystemZeroCodeLogic _zeroCodeLogic;
        private readonly ISystemPermissionLogic _permissionLogic;
        private readonly ICapPublisher _publisher;
        private readonly ISystemSerialNoLogic _systemSerialNoLogic;
        /// <summary>
        /// 
        /// </summary>
        public SystemWorkSheetLogic(ISystemSerialNoLogic systemSerialnoLogic, ICapPublisher publisher, ISystemDataBaseRepository dataBaseRepository,
            ISystemZeroCodeLogic zeroCodeLogic, ISystemPermissionLogic permissionLogic)
        {
            _systemSerialNoLogic = systemSerialnoLogic;
            _publisher = publisher;
            _dataBaseRepository = dataBaseRepository;
            _zeroCodeLogic = zeroCodeLogic;
            _permissionLogic = permissionLogic;
        }
        #endregion

        #region 表操作

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<bool>> TableExist(SystemWorkSheetTableExistInput input)
        {
            using (var fix = new SqlDatabaseFixture())
            {
                if (input.MenuId.HasValue)
                {
                    var config = await fix.Db.SystemZeroCode.SetSelect(s => new { s.ZeroCodeId }).FindAsync(f => f.MenuId == input.MenuId && f.Type == 2);
                    if (config != null)
                    {
                        input.ZeroCodeId = config.ZeroCodeId;
                    }
                }

                if (!input.ZeroCodeId.IsEmptyGuid())
                {
                    var form = await fix.Db.SystemZeroCode.SetSelect(s => new { s.TableName }).FindAsync(f => f.ZeroCodeId == input.ZeroCodeId);
                    if (form != null)
                    {
                        input.TableName = form.TableName;
                    }
                }
            }
            return OperateStatus<bool>.Success(await _dataBaseRepository.IsTableExist(input));
        }

        /// <summary>
        /// 创建表单
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public OperateStatus Table(SystemWorkSheetTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                switch (input.ConnectionType)
                {
                    case ResourceDataBaseType.Mysql:
                        operateStatus = TableMySql(input);
                        break;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            return operateStatus;
        }

        /// <summary>
        /// 创建表Mysql
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private OperateStatus TableMySql(SystemWorkSheetTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                string sql;
                using (var fix = new SqlDatabaseFixture())
                {
                    sql = $@"
                        CREATE TABLE {input.TableName}(
                            Id INT(50) primary key NOT NULL AUTO_INCREMENT COMMENT '主键',
	                        RowId char(36)  NOT NULL COMMENT '关联主键',
                            Status int  NOT NULL DEFAULT 1 COMMENT '0：未启用 1：正常 9：删除 999：彻底删除',
                            CreateTime datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	                        CreateUserName varchar(64) NULL COMMENT '创建人',
                            UpdateTime datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
	                        UpdateUserName varchar(64) NULL COMMENT '修改人',
                            CreateUserId char(36) NULL COMMENT '创建人Id',
                            CreateOrganizationId char(36) NULL COMMENT '创建人组织机构Id',
	                        CreateOrganizationName varchar(256) NULL COMMENT '创建人组织机构名称',
                            UpdateUserId char(36) NULL COMMENT '修改人Id',
	                        UpdateOrganizationId char(36) NULL COMMENT '修改人组织机构Id',
	                        UpdateOrganizationName varchar(256) NULL COMMENT '修改人组织机构名称'
                        )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='{input.Remark}';
                        alter table {input.TableName} convert to character set utf8mb4 collate utf8mb4_unicode_ci;
                      ";

                    new DbHelper(input.ConnectionString, input.ConnectionType).ExecuteSql(sql);
                    operateStatus.Msg = Chs.Successful;
                    operateStatus.Code = ResultCode.Success;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            return operateStatus;
        }

        /// <summary>
        /// 修改表别名称
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public OperateStatus TableRename(SystemWorkSheetTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                switch (input.ConnectionType)
                {
                    case ResourceDataBaseType.Mysql:
                        operateStatus = TableRenameMySql(input);
                        break;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            return operateStatus;
        }

        /// <summary>
        /// 创建表Mysql
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private OperateStatus TableRenameMySql(SystemWorkSheetTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                string sql;
                using (var fix = new SqlDatabaseFixture())
                {
                    var config = fix.Db.SystemZeroCode.SetSelect(s => new { s.TableName, s.MenuId }).Find(f => f.ZeroCodeId == input.ZeroCodeId);
                    //之前表是否存在
                    var isHaveTableName = IsTableExist(input.ConnectionString, input.ConnectionType, input.TableName);
                    if (!isHaveTableName.Data)
                    {
                        sql = $@"alter table {config.TableName} RENAME TO {input.TableName};";
                        var result = new DbHelper(input.ConnectionString, input.ConnectionType).ExecuteSql(sql);
                        fix.Db.SystemZeroCode.Update(f => f.ZeroCodeId == input.ZeroCodeId, new { TableName = input.TableName });
                    }
                    else
                    {
                        operateStatus.Msg = "已存在相同表别名：" + input.TableName;
                        return operateStatus;
                    }
                    operateStatus.Msg = Chs.Successful;
                    operateStatus.Code = ResultCode.Success;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            return operateStatus;
        }

        /// <summary>
        /// 保存表字段
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> TableField(SystemWorkSheetTableFieldInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //得到原有表字段
            try
            {
                var form = await _zeroCodeLogic.FindAsync(f => f.ZeroCodeId == input.ZeroCodeId);

                string connectionType = dataBase.ConnectionType;
                string connectionString = dataBase.ConnectionString;
                var columns = (await _dataBaseRepository.FindDataBaseColumns(new SystemDataBaseTableDto
                {
                    ConnectionString = dataBase.ConnectionString,
                    ConnectionType = dataBase.ConnectionType,
                    Name = form.TableName
                })).ToList();

                #region 排重
                var fields = input.Columns.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                var fieldsRepeats = fields.GroupBy(x => x.Model).Where(x => x.Count() > 1).ToList();
                if (fieldsRepeats.Any())
                {
                    string groupByStr = string.Empty;
                    foreach (var fieldsRepeat in fieldsRepeats)
                    {
                        groupByStr += fieldsRepeat.Key + ",";
                    }
                    operateStatus.Msg = "字段：" + groupByStr.TrimEnd(',') + " 重复,请修改后重试";
                    return operateStatus;
                }
                #endregion

                #region 获取新增字段
                IList<SystemWorkSheetTableFieldDetailInput> insert = new List<SystemWorkSheetTableFieldDetailInput>();
                columns = (columns.Where(w => w.Name != "Id" &&
                                              w.Name != "WorkflowStatus" &&
                                              w.Name != "WorkflowSn" &&
                                              w.Name != "WorkflowTitle" &&
                                              w.Name != "RowId" &&
                                              w.Name != "Status" &&
                                              w.Name != "CreateTime" &&
                                              w.Name != "CreateUserId" &&
                                              w.Name != "CreateUserName" &&
                                              w.Name != "CreateOrganizationId" &&
                                              w.Name != "CreateOrganizationName" &&
                                              w.Name != "UpdateTime" &&
                                              w.Name != "UpdateUserId" &&
                                              w.Name != "UpdateUserName" &&
                                              w.Name != "UpdateOrganizationId" &&
                                              w.Name != "UpdateOrganizationName")).ToList();

                //获取新增字段
                foreach (var field in fields)
                {
                    //不在则加入新增
                    if (columns.All(w => w.Name != field.ModelBefore))
                    {
                        insert.Add(field);
                    }
                }
                #endregion

                operateStatus = TableFieldMySql(connectionString, connectionType, form, fields, insert);

                ////处理刷新链路
                //var refresh = fields.Where(w => EnumZeroCodeControl.otherField.ToString() == w.Type
                //|| EnumZeroCodeControl.total.ToString() == w.Type
                //|| EnumZeroCodeControl.formulaNumber.ToString() == w.Type
                //|| EnumZeroCodeControl.textCombination.ToString() == w.Type);

                //处理刷新链路
                List<SystemZeroCodeReloadLink> reloadLinks = new List<SystemZeroCodeReloadLink>();

                //他表
                var otherFieldRefresh = fields.Where(w => EnumZeroCodeControl.otherField.ToString() == w.Type);
                foreach (var item in otherFieldRefresh)
                {
                    SystemZeroCodeReloadLink systemZeroCodeReloadLink = new SystemZeroCodeReloadLink()
                    {
                        ZeroCodeReloadLinkId = CombUtil.NewComb(),
                        FromZeroCodeId = input.ZeroCodeId,
                        TriggerZeroCodeId = (Guid)item.Options.ZeroCodeId,
                        TriggerZeroCodeKey = item.Options.Field,
                        ReloadZeroCodeId = input.ZeroCodeId,
                        ReloadZeroCodeKey = item.Key,
                        ReloadZeroCodeType = item.Type,
                    };
                    reloadLinks.Add(systemZeroCodeReloadLink);
                }

                //汇总
                var totalRefresh = fields.Where(w => EnumZeroCodeControl.total.ToString() == w.Type);
                foreach (var item in totalRefresh)
                {
                    SystemZeroCodeReloadLink systemZeroCodeReloadLink = new SystemZeroCodeReloadLink()
                    {
                        ZeroCodeReloadLinkId = CombUtil.NewComb(),
                        FromZeroCodeId = input.ZeroCodeId,
                        TriggerZeroCodeId = (Guid)item.Options.ZeroCodeId,
                        TriggerZeroCodeKey = item.Options.Field,
                        ReloadZeroCodeId = input.ZeroCodeId,
                        ReloadZeroCodeKey = item.Key,
                        ReloadZeroCodeType = item.Type,
                    };
                    reloadLinks.Add(systemZeroCodeReloadLink);
                }

                //公式
                var formulaNumberRefresh = fields.Where(w => EnumZeroCodeControl.formulaNumber.ToString() == w.Type);
                foreach (var item in formulaNumberRefresh)
                {
                    //得到公式里面有哪些字段
                    var code = ReplaceEditor(item.Options.Code);
                    var h1Elements = code.GetNodes();
                    if (h1Elements != null)
                    {
                        foreach (var element in h1Elements)
                        {
                            //解码
                            var columnName = HttpUtility.HtmlDecode(element.Id);
                            //转换key
                            var convert = JsonConvert.DeserializeObject<SystemWorkSheetFindElementKeyOutput>(columnName);
                            //得到转换值
                            SystemZeroCodeReloadLink systemZeroCodeReloadLink = new SystemZeroCodeReloadLink()
                            {
                                ZeroCodeReloadLinkId = CombUtil.NewComb(),
                                FromZeroCodeId = input.ZeroCodeId,
                                TriggerZeroCodeId = input.ZeroCodeId,
                                TriggerZeroCodeKey = convert.Key,
                                ReloadZeroCodeId = input.ZeroCodeId,
                                ReloadZeroCodeKey = item.Key,
                                ReloadZeroCodeType = item.Type,
                            };
                            reloadLinks.Add(systemZeroCodeReloadLink);
                        }
                    }
                }

                //文本组合
                var textCombinationRefresh = fields.Where(w => EnumZeroCodeControl.textCombination.ToString() == w.Type);
                foreach (var item in textCombinationRefresh)
                {
                    //得到公式里面有哪些字段
                    var code = ReplaceEditor(item.Options.Code);
                    var h1Elements = code.GetNodes();
                    if (h1Elements != null)
                    {
                        foreach (var element in h1Elements)
                        {
                            //解码
                            var columnName = HttpUtility.HtmlDecode(element.Id);
                            //转换key
                            var convert = JsonConvert.DeserializeObject<SystemWorkSheetFindElementKeyOutput>(columnName);
                            //得到转换值
                            SystemZeroCodeReloadLink systemZeroCodeReloadLink = new SystemZeroCodeReloadLink()
                            {
                                ZeroCodeReloadLinkId = CombUtil.NewComb(),
                                FromZeroCodeId = input.ZeroCodeId,
                                TriggerZeroCodeId = input.ZeroCodeId,
                                TriggerZeroCodeKey = convert.Key,
                                ReloadZeroCodeId = input.ZeroCodeId,
                                ReloadZeroCodeKey = item.Key,
                                ReloadZeroCodeType = item.Type,
                            };
                            reloadLinks.Add(systemZeroCodeReloadLink);
                        }
                    }
                }

                //插入规则
                using (var fix = new SqlDatabaseFixture())
                {
                    fix.Db.SystemZeroCodeReloadLink.Delete(d => d.FromZeroCodeId == input.ZeroCodeId);
                    if (reloadLinks.Any())
                    {
                        fix.Db.SystemZeroCodeReloadLink.BulkInsert(reloadLinks);
                    }
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 删除表字段
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> TableFieldDelete(SystemWorkSheetTableFieldInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                var form = await _zeroCodeLogic.FindAsync(f => f.ZeroCodeId == input.ZeroCodeId);
                string connectionType = dataBase.ConnectionType;
                string connectionString = dataBase.ConnectionString;
                string sql = $"ALTER TABLE  {form.TableName} DROP COLUMN {input.Columns};";
                new DbHelper(connectionString, connectionType).ExecuteSql(sql);
                operateStatus.Code = ResultCode.Success;
                operateStatus.Msg = Chs.Successful;
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 执行Mysql保存字段
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="form"></param>
        /// <param name="fields"></param>
        /// <param name="insert"></param>
        /// <returns></returns>
        private OperateStatus TableFieldMySql(
            string connectionString,
            string connectionType,
            SystemZeroCode form,
            IList<SystemWorkSheetTableFieldDetailInput> fields,
            IList<SystemWorkSheetTableFieldDetailInput> insert)
        {
            OperateStatus operateStatus = new OperateStatus();
            //得到原有表字段
            try
            {
                IList<SystemWorkSheetTableFieldDetailInput> modify = fields.Where(w => w.ModelBefore != w.Model).ToList();
                //修改字段长度或类型
                if (modify.Any())
                {
                    List<string> modifySql = new List<string>();
                    foreach (var ins in modify)
                    {
                        var sqlChange = ($"ALTER TABLE {form.TableName} CHANGE {ins.ModelBefore} {ins.Model} {ins.FieldType}  ");
                        modifySql.Add(sqlChange);
                        var sql = ($"ALTER TABLE {form.TableName} modify column {ins.Model} {ins.FieldType} NULL COMMENT '{ins.Label}{ins.Help}' ");
                        modifySql.Add(sql);
                    }
                    var modifyStr = modifySql.ExpandAndToString(";");
                    new DbHelper(connectionString, connectionType).ExecuteSql(modifyStr);
                }

                //新增字段
                if (insert.Any())
                {
                    List<string> insertSql = new List<string>();
                    foreach (var ins in insert)
                    {
                        var sql = ($"ALTER TABLE {form.TableName} ADD {ins.Model} {ins.FieldType} NULL COMMENT '{ins.Label}{ins.Help}' FIRST ");
                        insertSql.Insert(0, sql);
                    }
                    var createTableStr = insertSql.ExpandAndToString(";");
                    new DbHelper(connectionString, connectionType).ExecuteSql(createTableStr);
                }
                operateStatus.Code = ResultCode.Success;
                operateStatus.Msg = Chs.Successful;
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 判断数据库表是否存在
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="tb">数据表名</param>
        /// <returns>true:表示数据表已经存在；false，表示数据表不存在</returns>
        public OperateStatus<bool> IsTableExist(string connectionString, string connectionType, string tb)
        {
            string createDbStr = string.Empty;
            switch (connectionType)
            {
                case ResourceDataBaseType.Mysql:
                    string schema = connectionString.Split(";")[0].ToString().Split("=")[1];
                    createDbStr = $"select 1 from information_schema.TABLES t where t.TABLE_NAME ='{tb}' and table_schema='{schema}'";
                    break;
                case ResourceDataBaseType.Dm:
                    createDbStr = $"select 1 from  sysobjects where NAME='{tb}'";
                    break;
                case ResourceDataBaseType.Postgresql:
                    break;
                default:
                    createDbStr = $"select 1 from  sysobjects where  id = object_id('{tb}') and type = 'U'";
                    break;
            }
            //在指定的数据库中查找该表是否存在
            DataTable dt = new DbHelper(connectionString, connectionType).CreateSqlDataTable(createDbStr);
            return OperateStatus<bool>.Success(dt.Rows.Count != 0);
        }
        #endregion

        #region 数据操作
        /// <summary>
        /// 根据主键获取信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<IDictionary<string, object>>> FindById(SystemWorkSheetFindByIdInput input)
        {
            var zeroCode = (await _zeroCodeLogic.FindColumnJsonJsonById(new IdInput { Id = input.ZeroCodeId })).Data;
            var columns = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
            var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
            List<string> fields = new List<string>();
            //查询字段
            foreach (var item in columns)
            {
                fields.Add(item.Model + " " + item.Key);
            }
            foreach (var item in columns.Where(w => w.Type == "user"))
            {
                format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                {
                    Type = "User",
                    Key = item.Model
                });
            }
            string connectionType = dataBase.ConnectionType;
            string connectionString = dataBase.ConnectionString;
            //值对应的用户Id集合
            List<string> userIds = new List<string>();
            IDictionary<string, object> data = new Dictionary<string, object>();
            List<SystemUserHeaderOutput> user = new List<SystemUserHeaderOutput>();
            using (var db = GetConnectoin(connectionString, connectionType))
            {
                string sql = $"SELECT RowId,{fields.ExpandAndToString()} FROM {zeroCode.TableName} WHERE RowId='{input.Id}'";
                var findData = (await db.QueryAsync(sql)).ToList();

                foreach (var item in findData)
                {
                    foreach (var dd in item)
                    {
                        string dkey = dd.Key;
                        object dvalue = dd.Value;
                        if (format.Any())
                        {
                            var formatUsers = format.Any(a => a.Key == dkey);
                            if (formatUsers)
                            {

                                if (dvalue != null)
                                {
                                    var ids = dvalue.ToString().Split(',');
                                    foreach (var id in ids)
                                    {
                                        if (!userIds.Any(a => a == id))
                                        {
                                            userIds.Add(id);
                                        }
                                    }
                                }
                            }
                        }
                        data.Add(dkey, dvalue);
                    }
                }
            }
            if (userIds.Any())
            {
                //查询用户头像
                var sqlUser = $"select HeadImage,UserId from system_userinfo where UserId in ({userIds.ExpandAndToString().InSql()})";
                using (var fix = new SqlDatabaseFixture())
                {
                    user = (await fix.Db.Connection.QueryAsync<SystemUserHeaderOutput>(sqlUser)).ToList();
                }
            }
            //是否具有需要替换的Id,或者需要掩码
            if (userIds.Any())
            {
                IDictionary<string, object> tmp = new Dictionary<string, object>();
                foreach (var item in data)
                {
                    string dkey = item.Key;
                    object dvalue = item.Value;

                    if (dvalue != null)
                    {
                        //头像处理
                        var formatUsers = format.Any(a => a.Key == dkey);
                        if (formatUsers)
                        {
                            var userIdSplit = dvalue.ToString().Split(',');
                            List<string> userIdHeader = new List<string>();
                            foreach (var userId in userIdSplit)
                            {
                                userIdHeader.Add(user.FirstOrDefault(f => f.UserId.ToString() == userId)?.HeadImage);
                            }
                            tmp.Add(dkey + "_TxtHeader", userIdHeader.ExpandAndToString());
                        }
                    }
                    tmp.Add(dkey, dvalue);
                }
                data = tmp;
            }

            return OperateStatus<IDictionary<string, object>>.Success(data);
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<object>> Save(SystemWorkSheetSaveInput input)
        {
            OperateStatus<object> operateStatus = new OperateStatus<object>();

            List<SystemDataBaseSaveBusinessData> values = new List<SystemDataBaseSaveBusinessData>();
            if (input.Values.IsNotNullOrEmpty())
            {
                values = input.Values.JsonStringToList<SystemDataBaseSaveBusinessData>();
            }
            var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId })).Data;
            //所有涉及到的表名称
            List<string> tables = new List<string>();
            tables.Add(zeroCode.TableName);
            //新增/编辑参数
            var parameters = SaveParametersDefault(input);

            //所有动态执行Sql集合
            List<string> doSqls = new List<string>();
            try
            {
                var columns = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                //是否具有数据
                bool isAdd = SaveCheckIsAdd(zeroCode.TableName, input.RowId);
                //数据库所有列
                var allColumns = await SaveFindColumns(zeroCode.TableName);
                //得到所有需要处理的字段
                List<SystemWorkSheetTableFieldDetailInput> fields = SaveGetFields(zeroCode.ColumnJson, allColumns, values);
                //处理文本关联字段值
                SaveTextCombination(columns, allColumns, ref fields);
                List<Guid> addIds = new List<Guid>();
                //修改前数据
                OperateStatus<IDictionary<string, object>> updateBeforeData = new OperateStatus<IDictionary<string, object>>();
                if (isAdd)
                {
                    doSqls = await SaveAdd(input, zeroCode, fields, parameters, addIds);
                }
                else
                {
                    updateBeforeData = await FindById(new SystemWorkSheetFindByIdInput
                    {
                        Id = input.RowId,
                        ZeroCodeId = input.ZeroCodeId
                    });
                    doSqls = await SaveUpdate(input, zeroCode, fields, parameters, addIds);
                }
                operateStatus = SaveDoSqls(doSqls, parameters);
                if (operateStatus.Code == ResultCode.Success)
                {
                    await SaveSerialNo(isAdd, input, zeroCode, columns, fields, addIds);
                    SaveBarCode(input, zeroCode, columns, fields);
                    await SaveTotal(input, zeroCode, columns);
                    var updateAfterData = await FindById(new SystemWorkSheetFindByIdInput
                    {
                        Id = input.RowId,
                        ZeroCodeId = input.ZeroCodeId
                    });
                    SaveFormula(input, zeroCode, columns, updateAfterData.Data);

                    SaveSubscribe(isAdd, input, zeroCode, updateBeforeData.Data, updateAfterData.Data);
                    if (updateAfterData.Code == ResultCode.Success)
                    {
                        operateStatus.Data = updateAfterData.Data;
                    }
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = "操作失败:" + e.Message;
                operateStatus.Code = ResultCode.Error;
                return operateStatus;
            }

            SaveRemoveCache(tables);

            return operateStatus;
        }

        public OperateStatus<object> SaveDoSqls(List<string> doSqls,
            Dictionary<string, object> parameters)
        {
            LogWriter.Debug("批量执行语句：" + doSqls.ExpandAndToString(";"));
            OperateStatus<object> operateStatus = new OperateStatus<object>();
            //开启事务
            if (doSqls.Any())
            {
                using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
                {
                    var trans = connection.BeginTransaction();
                    try
                    {
                        var totalInstances = doSqls.Count();
                        int maxAllowedInstancesPerBatch = 30;
                        //总页数
                        int exceededTimes = (int)Math.Ceiling(Convert.ToDouble(totalInstances) / maxAllowedInstancesPerBatch);
                        if (exceededTimes > 1)
                        {
                            for (int i = 0; i <= exceededTimes; i++)
                            {
                                var skips = i * maxAllowedInstancesPerBatch;

                                if (skips >= totalInstances)
                                    break;

                                var items = doSqls.Skip(skips).Take(maxAllowedInstancesPerBatch);
                                connection.Execute(items.ExpandAndToString(";"), parameters, trans);
                            }
                        }
                        else
                        {
                            connection.Execute(doSqls.ExpandAndToString(";"), parameters, trans);
                        }

                        trans.Commit();
                        operateStatus.Code = ResultCode.Success;
                        operateStatus.Msg = Chs.Successful;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        operateStatus.Msg = "操作失败:" + ex.Message;
                        operateStatus.Code = ResultCode.Error;
                    }
                }
            }
            return operateStatus;
        }

        /// <summary>
        /// 清空表相关缓存
        /// </summary>
        /// <param name="tables"></param>
        private void SaveRemoveCache(List<string> tables)
        {
            //删除缓存
            if (tables.Any())
            {
                var caches = new List<string>();
                tables = tables.DistinctByExtension(d => d).ToList();
                foreach (var item in tables)
                {
                    StringBuilder cacheString = new StringBuilder();
                    foreach (var name in item.Split('_').Where(w => w != "_"))
                    {
                        cacheString.Append(char.ToUpper(name[0]) + name.Substring(1));
                    }
                    var cache = $"I" + cacheString.ToString() + "Logic_Cache:*";
                    var keys = SearchRedisKeys(cache);
                    if (keys.Any())
                    {
                        RedisHelper.Del(keys.ToArray());
                    }
                }
            }
        }

        /// <summary>
        /// 处理自动编码
        /// </summary>
        /// <param name="isAdd"></param>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        /// <param name="columns"></param>
        private async Task SaveSerialNo(
            bool isAdd,
            SystemWorkSheetSaveInput input,
            SystemZeroCode zeroCode,
            List<SystemWorkSheetTableFieldDetailInput> columns,
            List<SystemWorkSheetTableFieldDetailInput> fields,
            List<Guid> addIds)
        {
            if (isAdd)
            {
                //对单号进行清除操作
                var rows = columns.Where(a => a.Type == EnumZeroCodeControl.serialNo.ToString());
                if (rows != null && rows.Any())
                {
                    foreach (var row in rows)
                    {
                        if (!row.Options.LoadDisplay)
                        {
                            var noCreate = await _systemSerialNoLogic.Create(new SystemSerialCreateInput
                            {
                                Columns = columns,
                                Rule = row.Options.Rule.ObjectToJsonString(),
                                ZeroCodeId = input.ZeroCodeId,
                                LoadDisplay = row.Options.LoadDisplay,
                                Key = row.Key,
                                UserOccupation = row.Options.UserOccupation
                            });
                            //更新值
                            if (noCreate.Code == ResultCode.Success)
                            {
                                var column = columns.FirstOrDefault(f => f.Key == row.Key);
                                column.Value = noCreate.Data;
                                //更新
                                var noUpdate = new StringBuilder();
                                using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
                                {
                                    noUpdate.Append($"UPDATE {zeroCode.TableName} SET {row.Model}='{noCreate.Data}' WHERE RowId='{input.RowId}'");
                                    connection.Execute(noUpdate.ToString());
                                }
                            }
                        }
                        if (row.Options.UserOccupation)
                        {
                            //更新用户编码
                            _systemSerialNoLogic.Clear(input.ZeroCodeId, row.Key);
                        }
                    }
                }
            }
            //子表是否具有
            var subtable = columns.Where(a => a.Type == EnumZeroCodeControl.subtable.ToString());
            //是否具有自动编码
            foreach (var item in subtable)
            {
                //写入关联表
                if (addIds.Any())
                {
                    var zeroCodeId = (Guid)item.Options.ZeroCodeId;
                    var subtableForm = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = zeroCodeId })).Data;
                    var subtableTableName = subtableForm.TableName;
                    var subtableColumnJson = subtableForm.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    //得到需要编码控件
                    var subtableRows = subtableColumnJson.Where(a => a.Type == EnumZeroCodeControl.serialNo.ToString()).ToList();
                    //得到列表数据
                    var field = fields.FirstOrDefault(f => f.Key == item.Key);
                    var subtableData = field.Value.ToString().JsonStringToList<SystemZeroCodeControlCorrelationDatas>();

                    if (subtableRows != null && subtableRows.Any() && subtableData.Any())
                    {
                        foreach (var row in subtableRows)
                        {
                            for (int i = 0; i < subtableData.Count; i++)
                            {
                                //是否在ids里面
                                if (addIds.Contains(subtableData[i].RowId))
                                {
                                    if (!row.Options.LoadDisplay)
                                    {
                                        var noCreate = await _systemSerialNoLogic.Create(new SystemSerialCreateInput
                                        {
                                            Columns = subtableColumnJson,
                                            Rule = row.Options.Rule.ObjectToJsonString(),
                                            ZeroCodeId = zeroCodeId,
                                            LoadDisplay = row.Options.LoadDisplay,
                                            Key = row.Key,
                                            UserOccupation = row.Options.UserOccupation
                                        });
                                        //更新值
                                        if (noCreate.Code == ResultCode.Success)
                                        {
                                            var column = subtableColumnJson.FirstOrDefault(f => f.Key == row.Key);
                                            column.Value = noCreate.Data;
                                            //更新
                                            var noUpdate = new StringBuilder();
                                            using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
                                            {
                                                noUpdate.Append($"UPDATE {subtableForm.TableName} SET {row.Model}='{noCreate.Data}'  WHERE RowId='{subtableData[i].RowId}'");
                                                connection.Execute(noUpdate.ToString());
                                            }
                                        }
                                    }
                                    if (row.Options.UserOccupation)
                                    {
                                        //更新用户编码
                                        _systemSerialNoLogic.Clear(zeroCodeId, row.Key);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 更新汇总
        /// </summary>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        /// <param name="columns"></param>
        private async Task SaveTotal(
            SystemWorkSheetSaveInput input,
            SystemZeroCode zeroCode,
            List<SystemWorkSheetTableFieldDetailInput> columns)
        {
            //更新
            using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
            {
                var rows = columns.Where(a => a.Type == EnumZeroCodeControl.total.ToString());
                if (rows != null && rows.Any())
                {
                    foreach (var row in rows)
                    {
                        //获取汇总字段
                        var correction = columns.FirstOrDefault(f => f.Key == row.Options.Key);
                        //获取查询的关联表
                        var zeroCodeId = (Guid)correction.Options.ZeroCodeId;
                        //关联表字段
                        var correctionForm = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = zeroCodeId })).Data;
                        var correctionTableName = correctionForm.TableName;
                        var correctionColumnJson = correctionForm.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                        var otherCollection = correctionColumnJson.FirstOrDefault(f => f.Key == row.Options.Field);
                        if (otherCollection != null)
                        {
                            var updateSql = "";
                            //判断关联字段是否是双向关联
                            if (correction.Options.TwoWay.Is)
                            {
                                string valueSql = "";
                                var otherCollectionFind = correctionColumnJson.FirstOrDefault(f => f.Options.TwoWay.Key == correction.Key);
                                if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.total).ToString())
                                {
                                    valueSql = $"select IFNULL(sum({otherCollection.Model}),0 )as number from {correctionTableName} where Status=1 and  JSON_CONTAINS({otherCollectionFind.Model},JSON_ARRAY(\"{input.RowId}\"))";
                                }
                                else if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.nu).ToString())
                                {
                                    valueSql = $"select IFNULL(count({otherCollection.Model}),0 )as number from {correctionTableName} where Status=1 and JSON_CONTAINS({otherCollectionFind.Model},JSON_ARRAY(\"{input.RowId}\")) and {otherCollectionFind.Model}!=null";
                                }
                                else if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.nu).ToString())
                                {
                                    valueSql = $"select IFNULL(count({otherCollection.Model}),0 )as number from {correctionTableName} where Status=1 and JSON_CONTAINS({otherCollectionFind.Model},JSON_ARRAY(\"{input.RowId}\")) and {otherCollectionFind.Model}=null";
                                }
                                if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.avg).ToString())
                                {
                                    valueSql = $"select IFNULL(avg({otherCollection.Model}),0 )as number from {correctionTableName} where Status=1 and  JSON_CONTAINS({otherCollectionFind.Model},JSON_ARRAY(\"{input.RowId}\"))";
                                }
                                if (valueSql.IsNotNullOrEmpty())
                                {
                                    updateSql = $"update {zeroCode.TableName} SET {row.Model}=({valueSql}) where   RowId='{input.RowId}'";
                                }
                            }
                            else
                            {
                                string valueSql = "";
                                var otherCollectionFind = correctionColumnJson.FirstOrDefault(f => f.Options.TwoWay.Key == correction.Key);
                                if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.total).ToString())
                                {
                                    valueSql = $"select IFNULL(sum({otherCollection.Model}), 0 )as number from {correctionTableName} where Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE({correction.Model},'$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}')";
                                }
                                else if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.nu).ToString())
                                {
                                    valueSql = $"select IFNULL(count({otherCollection.Model}), 0 )as number from {correctionTableName} where Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE({correction.Model},'$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}') and {otherCollection.Model}!=null ";
                                }
                                else if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.nu).ToString())
                                {
                                    valueSql = $"select IFNULL(count({otherCollection.Model}), 0 )as number from {correctionTableName} where Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE({correction.Model},'$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}') and {otherCollection.Model}=null";
                                }
                                if (row.Options.Type == ((int)EnumZeroCodeControlTotalType.avg).ToString())
                                {
                                    valueSql = $"select IFNULL(avg({otherCollection.Model}), 0 )as number from {correctionTableName} where Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE({correction.Model},'$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}')";
                                }
                                if (valueSql.IsNotNullOrEmpty())
                                {
                                    //根据当前的Id查询所有表
                                    var data = connection.Query<int>(valueSql);
                                    updateSql = $"update {zeroCode.TableName} SET {row.Model}={data.FirstOrDefault()} where Status=1 and RowId='{input.RowId}'";
                                }
                            }
                            var updateResult = connection.Execute(updateSql);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 更新公式值
        /// </summary>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        /// <param name="columns"></param>
        private void SaveFormula(
            SystemWorkSheetSaveInput input,
            SystemZeroCode zeroCode,
            List<SystemWorkSheetTableFieldDetailInput> columns,
           dynamic value)
        {
            //更新
            using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
            {
                var rows = columns.Where(a => a.Type == EnumZeroCodeControl.formulaNumber.ToString());
                if (rows != null && rows.Any())
                {
                    foreach (var row in rows)
                    {
                        string param = "";
                        //解析Code
                        foreach (var dd in value)
                        {
                            string dkey = dd.Key;
                            object dvalue = dd.Value;
                            //判断类型
                            var rowFind = columns.FirstOrDefault(a => a.Model == dkey);
                            if (rowFind != null)
                            {
                                if (rowFind.Type == EnumZeroCodeControl.total.ToString() ||
                                    rowFind.Type == EnumZeroCodeControl.enumber.ToString())
                                {
                                    param += "var " + dkey + "=" + dvalue + ";";
                                }
                                else
                                {
                                    param += "var " + dkey + "='" + dvalue + "';";
                                }
                            }
                        }
                        var engine = new ScriptEngine();
                        //解析表达式
                        var code = ReplaceEditor(row.Options.Code);
                        var h1Elements = code.GetNodes();
                        if (h1Elements != null)
                        {
                            foreach (var element in h1Elements)
                            {
                                //解码
                                var columnName = HttpUtility.HtmlDecode(element.Id);
                                //转换key
                                var convert = JsonConvert.DeserializeObject<SystemWorkSheetFindElementKeyOutput>(columnName);
                                //得到转换值
                                code = code.Replace(element.OuterHtml, convert.Key);
                            }
                            param += code;
                            var result = engine.Evaluate(param);
                            var updateSql = $"update {zeroCode.TableName} SET {row.Model}={result} where Status=1 and RowId='{input.RowId}'";
                            var updateResult = connection.Execute(updateSql);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 替换Html标签
        /// </summary>
        /// <param name="value"></param>
        /// <param name="length"></param>
        /// <returns>替换后的字符串</returns>
        public string ReplaceEditor(string value)
        {
            return value.Replace("<p>", "").Replace("</p>", "").Replace("<div class=\"KDefaultSpanTag\">", "").Replace("</div>", "").Replace("class=\"KDefaultSpan\" ", "");
        }

        /// <summary>
        /// 处理自动编码
        /// </summary>
        /// <param name="isAdd"></param>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        /// <param name="columns"></param>
        /// <param name="connection"></param>
        private void SaveBarCode(
            SystemWorkSheetSaveInput input,
            SystemZeroCode zeroCode,
            List<SystemWorkSheetTableFieldDetailInput> columns,
             List<SystemWorkSheetTableFieldDetailInput> fields)
        {
            //对单号进行清除操作
            var rows = columns.Where(a => a.Type == "dataShow" && a.Options.DefaultValue.ToString().IsNotNullOrEmpty()).ToList();
            if (rows != null && rows.Any())
            {
                foreach (var row in rows)
                {
                    string content = row.Options.DefaultValue.ToString().ReplaceEditor();
                    var h1Elements = row.Options.DefaultValue.ToString().GetNodes();
                    if (h1Elements != null)
                    {
                        foreach (var element in h1Elements)
                        {
                            //解码
                            var columnSetting = HttpUtility.UrlDecode(element.Id);
                            if (columnSetting != null)
                            {
                                string value = "";
                                //得到对应节点值
                                switch (columnSetting)
                                {
                                    case "RowId":
                                        value = input.RowId.ToString();
                                        break;
                                    case "CreateTime":
                                        value = DateTime.Now.ToString();
                                        break;
                                    case "CreateUserId":
                                        value = input.UserId.ToString();
                                        break;
                                    case "CreateUserName":
                                        value = input.UserName.ToString();
                                        break;
                                    case "CreateOrganizationId":
                                        value = input.OrganizationId.ToString();
                                        break;
                                    case "CreateOrganizationName":
                                        value = input.OrganizationName.ToString();
                                        break;
                                    default:
                                        var column = fields.FirstOrDefault(f => f.Key == columnSetting);
                                        value = column?.Value.ToString();
                                        break;
                                }
                                //得到值
                                content = content.Replace(element.OuterHtml, value);
                            }
                        }
                    }

                    //更新
                    using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
                    {
                        var noUpdate = new StringBuilder();
                        noUpdate.Append($"UPDATE {zeroCode.TableName} SET {row.Key}='{content}' WHERE RowId='{input.RowId}'");
                        connection.Execute(noUpdate.ToString());
                    }
                }
            }
        }

        /// <summary>
        /// 保存订阅
        /// </summary>
        /// <param name="isAdd"></param>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        private void SaveSubscribe(bool isAdd, SystemWorkSheetSaveInput input, SystemZeroCode zeroCode, IDictionary<string, object> updateBeforeData, IDictionary<string, object> updateAfterData)
        {
            SystemZeroCodeDataLog systemZeroCodeDataLog = new SystemZeroCodeDataLog();
            if (isAdd)
            {
                List<SystemZeroCodeDataLogValue> systemZeroCodeDataLogValues = new List<SystemZeroCodeDataLogValue>();
                systemZeroCodeDataLogValues.Add(new SystemZeroCodeDataLogValue
                {
                    TableName = zeroCode.TableName,
                    Description = zeroCode.Remark,
                    Data = updateAfterData
                });
                systemZeroCodeDataLog = new SystemZeroCodeDataLog()
                {
                    Type = EnumZeroCodeDataLogType.新增.ToShort(),
                    ZeroCodeDataLogId = CombUtil.NewComb(),
                    CreateUserId = input.UserId,
                    CreateUserName = input.UserName,
                    CreateTime = DateTime.Now,
                    RowId = input.RowId,
                    ZeroCodeId = input.ZeroCodeId,
                    Before = JsonConvert.SerializeObject(systemZeroCodeDataLogValues),
                };
            }
            else
            {
                //修改
                List<SystemZeroCodeDataLogValue> systemZeroCodeDataLogValuesBefore = new List<SystemZeroCodeDataLogValue>();
                systemZeroCodeDataLogValuesBefore.Add(new SystemZeroCodeDataLogValue
                {
                    TableName = zeroCode.TableName,
                    Description = zeroCode.Remark,
                    Data = updateBeforeData
                });

                List<SystemZeroCodeDataLogValue> systemZeroCodeDataLogValuesAfter = new List<SystemZeroCodeDataLogValue>();
                systemZeroCodeDataLogValuesAfter.Add(new SystemZeroCodeDataLogValue
                {
                    TableName = zeroCode.TableName,
                    Description = zeroCode.Remark,
                    Data = updateAfterData
                });
                systemZeroCodeDataLog = new SystemZeroCodeDataLog()
                {
                    Type = EnumZeroCodeDataLogType.编辑.ToShort(),
                    ZeroCodeDataLogId = CombUtil.NewComb(),
                    CreateUserId = input.UserId,
                    CreateUserName = input.UserName,
                    CreateTime = DateTime.Now,
                    RowId = input.RowId,
                    ZeroCodeId = input.ZeroCodeId,
                    Before = JsonConvert.SerializeObject(systemZeroCodeDataLogValuesBefore),
                    After = JsonConvert.SerializeObject(systemZeroCodeDataLogValuesAfter)
                };
            }
            _publisher.Publish("ZeroCodeDataLogSaveSubscribe", systemZeroCodeDataLog);
        }

        /// <summary>
        /// 保存新增Sql集合
        /// </summary>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        /// <param name="fields"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private async Task<List<string>> SaveAdd(SystemWorkSheetSaveInput input,
            SystemZeroCode zeroCode,
            List<SystemWorkSheetTableFieldDetailInput> fields,
            Dictionary<string, object> parameters,
            List<Guid> addIds)
        {
            List<string> doSqls = new List<string>();
            string symbol = RepositoryUtil.GetSymbol();
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder stringBuilderValues = new StringBuilder();
            stringBuilder.Append($"INSERT INTO {zeroCode.TableName} (");
            if (fields.Any())
            {
                #region 主表
                var insertControls = fields.Where(item => !item.Value.IsNullOrEmpty()).ToList();
                foreach (var item in insertControls)
                {
                    stringBuilder.Append($"{item.Model},");
                    //过滤Xss攻击
                    item.Value = _filterSqlField.Any(f => f == item.Type) ? item.Value.ToString().Xss() : item.Value;
                    if (item.Type == EnumZeroCodeControl.@switch.ToString())
                    {
                        parameters.Add($"{item.Key}", item.Value.ToString().IsNotNullOrEmpty() ? Convert.ToBoolean(item.Value) : false);
                    }
                    //子表
                    else if (item.Type == EnumZeroCodeControl.subtable.ToString() && item.Options.ZeroCodeId.HasValue)
                    {
                        IList<Guid> subtableRowIds = new List<Guid>();
                        var subtableForm = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)item.Options.ZeroCodeId })).Data;
                        var subtableTableName = subtableForm.TableName;
                        var subtableColumnJson = subtableForm.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                        //得到列表数据
                        var subtableData = item.Value.ToString().JsonStringToList<SystemZeroCodeControlCorrelationDatas>();
                        //写入关联表
                        if (subtableData.Any())
                        {
                            for (var i = 0; i < subtableData.Count; i++)
                            {
                                var subtableItem = subtableData[i];
                                var subtableBuilder = new StringBuilder();
                                var subtableBuilderValues = new StringBuilder();
                                var subtableRowId = CombUtil.NewComb();
                                subtableBuilder.Append($"INSERT INTO {subtableTableName} (");
                                var mode = "";
                                foreach (var detail in subtableItem.Data)
                                {
                                    //非RowId
                                    if (detail.Key.IsNotNullOrEmpty() && detail.Value.IsNotNullOrEmpty() && detail.Key != "RowId")
                                    {
                                        var detailColumn = subtableColumnJson.FirstOrDefault(f => f.Key == detail.Key);
                                        if (detailColumn != null)
                                        {
                                            subtableBuilder.Append($"{detailColumn.Model},");
                                            subtableBuilderValues.Append($"{symbol}{subtableTableName}{i}{detail.Key},");

                                            if (detailColumn.Type == EnumZeroCodeControl.@switch.ToString())
                                            {
                                                parameters.Add($"{subtableTableName}{i}{detail.Key}", detail.Value.ToString().IsNotNullOrEmpty() ? Convert.ToBoolean(detail.Value) : false);
                                            }
                                            //双向关联
                                            else if (detailColumn.Type == EnumZeroCodeControl.correlation.ToString() && item.Options.ZeroCodeId.HasValue)
                                            {
                                                //判断是否和当前表关联
                                                if (input.ZeroCodeId == item.Options.ZeroCodeId)
                                                {
                                                    mode = detail.Key;
                                                    parameters.Add($"{subtableTableName}{i}{detail.Key}", input.RowId);
                                                }
                                                else
                                                {
                                                    //是否为JSON格式
                                                    if (detail.Value.Contains("[") && detail.Value.Contains("]"))
                                                    {
                                                        parameters.Add($"{subtableTableName}{i}{detail.Key}", detail.Value);
                                                    }
                                                    else
                                                    {
                                                        parameters.Add($"{subtableTableName}{i}{detail.Key}", "[\"" + detail.Value + "\"]");
                                                    }
                                                }
                                            }
                                            else
                                            {
                                                parameters.Add($"{subtableTableName}{i}{detail.Key}", detail.Value);
                                            }
                                        }
                                    }
                                    else if (detail.Key == "RowId")
                                    {
                                        subtableBuilder.Append("RowId,");

                                        var detailRowId = Guid.Parse(detail.Value.IsNotNullOrEmpty() ? detail.Value : subtableRowId.ToString());
                                        var key = $"{subtableTableName}{i}RowId";
                                        subtableBuilderValues.Append($"{symbol}{key},");
                                        parameters.Add($"{key}", detailRowId);
                                        subtableRowIds.Add(detailRowId);
                                        addIds.Add(detailRowId);
                                    }
                                }

                                //双向关联数据处理
                                var check = subtableColumnJson.FirstOrDefault(f => f.Options.ZeroCodeId == input.ZeroCodeId);
                                if (check != null && mode.IsNullOrEmpty())
                                {
                                    subtableBuilder.Append($"{check.Model},");
                                    subtableBuilderValues.Append($"{symbol}{subtableTableName}{i}{check.Key},");
                                    var listRowId = new List<Guid>() { input.RowId };
                                    parameters.Add($"{subtableTableName}{i}{check.Key}", listRowId.ListToJsonString());
                                }

                                subtableBuilder.Append("CreateTime,");
                                subtableBuilderValues.Append($"{symbol}CreateTime,");

                                subtableBuilder.Append("CreateUserId,");
                                subtableBuilderValues.Append($"{symbol}CreateUserId,");

                                subtableBuilder.Append("CreateUserName,");
                                subtableBuilderValues.Append($"{symbol}CreateUserName,");

                                subtableBuilder.Append("CreateOrganizationId,");
                                subtableBuilderValues.Append($"{symbol}CreateOrganizationId,");

                                subtableBuilder.Append("CreateOrganizationName,");
                                subtableBuilderValues.Append($"{symbol}CreateOrganizationName,");

                                subtableBuilder.Append("UpdateUserId,");
                                subtableBuilderValues.Append($"{symbol}UpdateUserId,");

                                subtableBuilder.Append("UpdateUserName,");
                                subtableBuilderValues.Append($"{symbol}UpdateUserName,");

                                subtableBuilder.Append("UpdateOrganizationId,");
                                subtableBuilderValues.Append($"{symbol}UpdateOrganizationId,");

                                subtableBuilder.Append("UpdateOrganizationName,");
                                subtableBuilderValues.Append($"{symbol}UpdateOrganizationName,");

                                subtableBuilder.Append("Status,");
                                subtableBuilderValues.Append($"1,");

                                string batchsql = subtableBuilder.ToString().TrimEnd(',') + " ) VALUES (" + subtableBuilderValues.ToString().TrimEnd(',') + ")";
                                if (batchsql.IsNotNullOrEmpty())
                                {
                                    doSqls.Add(batchsql);
                                }
                            }
                        }
                        //添加
                        parameters.Add($"{item.Key}", subtableRowIds.ListToJsonString());
                    }
                    else if (item.Type == EnumZeroCodeControl.correlation.ToString() && item.Options.ZeroCodeId.HasValue)
                    {
                        var correlationForm = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)item.Options.ZeroCodeId })).Data;
                        var correlationTableName = correlationForm.TableName;
                        var correlationColumnJson = correlationForm.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                        //得到列表数据
                        var correlationData = item.Value.ToString().JsonStringToList<Guid>();

                        //判断是否为双向关联
                        if (item.Options.TwoWay.Is && correlationData.Any())
                        {
                            //查询双向关联字段
                            var twoWay = correlationColumnJson.FirstOrDefault(f => f.Key == item.Options.TwoWay.Key);
                            if (twoWay != null)
                            {
                                doSqls.Add($"UPDATE {correlationForm.TableName} SET {twoWay.Model} = '[]' WHERE {twoWay.Model} is null");
                                //更新字段值
                                doSqls.Add($"UPDATE {correlationForm.TableName} SET {twoWay.Model} = JSON_ARRAY_APPEND({twoWay.Model}, '$', '{input.RowId}') WHERE RowId in ({correlationData.ExpandAndToString().InSql()}) and JSON_CONTAINS({twoWay.Model}, '\"{input.RowId}\"', '$') = 0");
                            }
                        }
                        else
                        {
                            parameters.Add($"{item.Key}", item.Value);
                        }
                    }
                    else
                    {
                        parameters.Add($"{item.Key}", item.Value);
                    }
                    stringBuilderValues.Append($"{symbol}{item.Key},");
                }
                #endregion
            }

            //主键
            stringBuilder.Append("RowId,");
            stringBuilderValues.Append($"{symbol}RowId,");

            stringBuilder.Append("CreateTime,");
            stringBuilderValues.Append($"{symbol}CreateTime,");

            stringBuilder.Append("CreateUserId,");
            stringBuilderValues.Append($"{symbol}CreateUserId,");

            stringBuilder.Append("CreateUserName,");
            stringBuilderValues.Append($"{symbol}CreateUserName,");

            stringBuilder.Append("CreateOrganizationId,");
            stringBuilderValues.Append($"{symbol}CreateOrganizationId,");

            stringBuilder.Append("CreateOrganizationName,");
            stringBuilderValues.Append($"{symbol}CreateOrganizationName,");

            stringBuilder.Append("UpdateUserId,");
            stringBuilderValues.Append($"{symbol}UpdateUserId,");

            stringBuilder.Append("UpdateUserName,");
            stringBuilderValues.Append($"{symbol}UpdateUserName,");

            stringBuilder.Append("UpdateOrganizationId,");
            stringBuilderValues.Append($"{symbol}UpdateOrganizationId,");

            stringBuilder.Append("UpdateOrganizationName,");
            stringBuilderValues.Append($"{symbol}UpdateOrganizationName,");

            stringBuilder.Append("Status,");
            stringBuilderValues.Append($"1,");

            //拼接Sql
            var sql = stringBuilder.ToString().TrimEnd(',') + " ) VALUES (" +
                   stringBuilderValues.ToString().TrimEnd(',') + ")";

            doSqls.Add(sql);

            return doSqls;
        }

        /// <summary>
        /// 保存新增Sql集合
        /// </summary>
        /// <param name="input"></param>
        /// <param name="zeroCode"></param>
        /// <param name="fields"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private async Task<List<string>> SaveUpdate(SystemWorkSheetSaveInput input,
            SystemZeroCode zeroCode,
            List<SystemWorkSheetTableFieldDetailInput> fields,
            Dictionary<string, object> parameters,
            List<Guid> addIds)
        {
            List<string> doSqls = new List<string>();
            string symbol = RepositoryUtil.GetSymbol();
            using (IDbConnection connection = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
            {
                List<SystemZeroCodeDataLogContent> systemZeroCodeDataLogContents = new List<SystemZeroCodeDataLogContent>();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($"UPDATE {zeroCode.TableName} SET ");
                if (fields.Any())
                {
                    #region 主表
                    var updateControls = fields.Where(item => item.Key.ToUpper() != "STATUS"
                                                              && item.Type.ToUpper() != "IMAGESHOW").ToList();
                    foreach (var item in updateControls)
                    {
                        item.Value = _filterSqlField.Any(f => f == item.Type) ?
                            (item.Value != null ? item.Value.ToString().Xss() : null)
                            : item.Value;
                        stringBuilder.Append($"{item.Model}={symbol}{item.Key},");
                        if (item.Type == EnumZeroCodeControl.@switch.ToString())
                        {
                            parameters.Add($"{symbol}{item.Key}", item.Value != null ? Convert.ToBoolean(item.Value) : false);
                        }
                        else if (item.Type == EnumZeroCodeControl.subtable.ToString() && item.Options.ZeroCodeId.HasValue)
                        {
                            var subtableForm = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)item.Options.ZeroCodeId })).Data;
                            var subtableTableName = subtableForm.TableName;
                            var subtableColumnJson = subtableForm.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();

                            var gridData = item.Value.ToString().JsonStringToList<SystemZeroCodeControlCorrelationDatas>();
                            //查找所有关联数据
                            var allSubtableData = $"SELECT RowId from {subtableTableName} where Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE ({item.Model}, '$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}')";
                            var allSubtableDataList = await connection.QueryAsync<Guid>(allSubtableData, new { });

                            List<Guid> rowIds = new List<Guid>();
                            List<Guid> deleteIds = new List<Guid>();
                            List<Guid> updateIds = new List<Guid>();
                            //查找删除数据，新增数据，然后再进行修改数据
                            if (gridData.Any())
                            {
                                //得到所有界面传递的子表Id
                                rowIds = gridData.Select(a => a.RowId).ToList();
                            }
                            //查询出删除的数据
                            foreach (var correlationData in allSubtableDataList)
                            {
                                var check = rowIds.Any(a => a == correlationData);
                                if (!check)
                                {
                                    deleteIds.Add(correlationData);
                                }
                            }
                            foreach (var rowId in rowIds)
                            {
                                var check = allSubtableDataList.Any(a => a == rowId);
                                if (check)
                                {
                                    updateIds.Add(rowId);
                                }
                                else
                                {
                                    addIds.Add(rowId);
                                }
                            }
                            if (deleteIds.Any())
                            {
                                //更新删除状态
                                foreach (var deleteId in deleteIds)
                                {
                                    //设置更新为删除状态
                                    doSqls.Add($"UPDATE {subtableTableName} SET Status=9 where RowId='{deleteId}'");
                                }
                            }
                            //新增数据
                            if (addIds.Any())
                            {
                                var addGridData = gridData.Where(w => addIds.Contains(w.RowId)).ToList();

                                for (var i = 0; i < addGridData.Count; i++)
                                {
                                    var gridItem = addGridData[i];

                                    var subtableBuilder = new StringBuilder();
                                    var subtableBuilderValues = new StringBuilder();
                                    var detailId = CombUtil.NewComb();
                                    subtableBuilder.Append($"INSERT INTO {subtableTableName} (");
                                    var mode = "";
                                    foreach (var detail in gridItem.Data)
                                    {
                                        //非RowId
                                        if (detail.Key.IsNotNullOrEmpty() && detail.Value.IsNotNullOrEmpty() && detail.Key != "RowId")
                                        {
                                            var detailColumn = subtableColumnJson.FirstOrDefault(f => f.Key == detail.Key);
                                            if (detailColumn != null)
                                            {
                                                subtableBuilder.Append($"{detailColumn.Model},");
                                                var key = $"{subtableTableName}{i}{detail.Key}";
                                                subtableBuilderValues.Append($"{symbol}{key},");

                                                if (detailColumn.Type == EnumZeroCodeControl.@switch.ToString())
                                                {
                                                    parameters.Add($"{key}", detail.Value.ToString().IsNotNullOrEmpty() ? Convert.ToBoolean(detail.Value) : false);
                                                }
                                                //双向关联
                                                else if (detailColumn.Type == EnumZeroCodeControl.correlation.ToString() && item.Options.ZeroCodeId.HasValue)
                                                {
                                                    //判断是否和当前表关联
                                                    if (input.ZeroCodeId == item.Options.ZeroCodeId)
                                                    {
                                                        mode = detail.Key;
                                                        parameters.Add($"{key}", input.RowId);
                                                    }
                                                    else
                                                    {
                                                        //是否为JSON格式
                                                        if (detail.Value.Contains("[") && detail.Value.Contains("]"))
                                                        {
                                                            parameters.Add($"{key}", detail.Value);
                                                        }
                                                        else
                                                        {
                                                            parameters.Add($"{key}", "[\"" + detail.Value + "\"]");
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    parameters.Add($"{key}", detail.Value);
                                                }
                                            }
                                        }
                                        else if (detail.Key == "RowId")
                                        {
                                            subtableBuilder.Append("RowId,");

                                            var detailValue = detail.Value.IsNotNullOrEmpty() ? detail.Value : detailId.ToString();
                                            var key = $"{subtableTableName}{i}RowId";
                                            subtableBuilderValues.Append($"{symbol}{key},");
                                            parameters.Add($"{key}", detailValue);
                                        }
                                    }

                                    //双向关联数据处理
                                    var check = subtableColumnJson.FirstOrDefault(f => f.Options.ZeroCodeId == input.ZeroCodeId);
                                    if (check != null && mode.IsNullOrEmpty())
                                    {
                                        subtableBuilder.Append($"{check.Model},");
                                        subtableBuilderValues.Append($"{symbol}{subtableTableName}{i}{check.Key},");
                                        var listRowId = new List<Guid>() { input.RowId };
                                        parameters.Add($"{subtableTableName}{i}{check.Key}", listRowId.ListToJsonString());
                                    }

                                    subtableBuilder.Append("CreateTime,");
                                    subtableBuilderValues.Append($"{symbol}CreateTime,");

                                    subtableBuilder.Append("CreateUserId,");
                                    subtableBuilderValues.Append($"{symbol}CreateUserId,");

                                    subtableBuilder.Append("CreateUserName,");
                                    subtableBuilderValues.Append($"{symbol}CreateUserName,");

                                    subtableBuilder.Append("CreateOrganizationId,");
                                    subtableBuilderValues.Append($"{symbol}CreateOrganizationId,");

                                    subtableBuilder.Append("CreateOrganizationName,");
                                    subtableBuilderValues.Append($"{symbol}CreateOrganizationName,");

                                    subtableBuilder.Append("UpdateUserId,");
                                    subtableBuilderValues.Append($"{symbol}UpdateUserId,");

                                    subtableBuilder.Append("UpdateUserName,");
                                    subtableBuilderValues.Append($"{symbol}UpdateUserName,");

                                    subtableBuilder.Append("UpdateOrganizationId,");
                                    subtableBuilderValues.Append($"{symbol}UpdateOrganizationId,");

                                    subtableBuilder.Append("UpdateOrganizationName,");
                                    subtableBuilderValues.Append($"{symbol}UpdateOrganizationName,");

                                    subtableBuilder.Append("Status,");
                                    subtableBuilderValues.Append($"1,");

                                    string batchsql = subtableBuilder.ToString().TrimEnd(',') + " ) VALUES (" + subtableBuilderValues.ToString().TrimEnd(',') + ")";
                                    if (batchsql.IsNotNullOrEmpty())
                                    {
                                        doSqls.Add(batchsql);
                                    }
                                }
                            }
                            //更新数据
                            if (updateIds.Any())
                            {
                                var updateGridData = gridData.Where(w => updateIds.Contains(w.RowId)).ToList();
                                for (var i = 0; i < updateGridData.Count; i++)
                                {
                                    StringBuilder stringBuilderCorrelation = new StringBuilder();
                                    StringBuilder subtableBuilder = new StringBuilder();
                                    stringBuilderCorrelation.Append($"UPDATE {subtableTableName} SET ");
                                    var updateGridDataItem = updateGridData[i];
                                    foreach (var detail in updateGridDataItem.Data)
                                    {
                                        //非RowId
                                        if (detail.Key.IsNotNullOrEmpty() && detail.Value.IsNotNullOrEmpty() && detail.Key != "RowId")
                                        {
                                            var detailColumn = subtableColumnJson.FirstOrDefault(f => f.Key == detail.Key);
                                            if (detailColumn != null)
                                            {
                                                var key = $"{subtableTableName}{i}{detail.Key}_Update";
                                                stringBuilderCorrelation.Append($"{detailColumn.Model}={symbol}{key},");

                                                if (detailColumn.Type == EnumZeroCodeControl.@switch.ToString())
                                                {
                                                    parameters.Add($"{key}", detail.Value.ToString().IsNotNullOrEmpty() ? Convert.ToBoolean(detail.Value) : false);
                                                }
                                                //双向关联
                                                else if (detailColumn.Type == EnumZeroCodeControl.correlation.ToString() && item.Options.ZeroCodeId.HasValue)
                                                {
                                                    //判断是否和当前表关联
                                                    if (input.ZeroCodeId == item.Options.ZeroCodeId)
                                                    {
                                                        parameters.Add($"{key}", input.RowId);
                                                    }
                                                    else
                                                    {//是否为JSON格式
                                                        if (detail.Value.Contains("[") && detail.Value.Contains("]"))
                                                        {
                                                            parameters.Add($"{key}", detail.Value);
                                                        }
                                                        else
                                                        {
                                                            parameters.Add($"{key}", "[\"" + detail.Value + "\"]");
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    parameters.Add($"{key}", detail.Value);
                                                }
                                            }
                                        }
                                    }

                                    stringBuilderCorrelation.Append($"UpdateTime={symbol}UpdateTime,");
                                    stringBuilderCorrelation.Append($"UpdateUserId={symbol}UpdateUserId,");
                                    stringBuilderCorrelation.Append($"UpdateUserName={symbol}UpdateUserName,");
                                    stringBuilderCorrelation.Append($"UpdateOrganizationId={symbol}UpdateOrganizationId,");
                                    stringBuilderCorrelation.Append($"UpdateOrganizationName={symbol}UpdateOrganizationName");

                                    stringBuilderCorrelation.Append($" where RowId='{updateGridDataItem.RowId}'");
                                    doSqls.Add(stringBuilderCorrelation.ToString());
                                }
                            }

                            parameters.Add($"{symbol}{item.Model}", rowIds.ListToJsonString());


                        }
                        else if (item.Type == EnumZeroCodeControl.correlation.ToString() && item.Options.ZeroCodeId.HasValue)
                        {
                            var correlationForm = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)item.Options.ZeroCodeId })).Data;
                            var correlationTableName = correlationForm.TableName;
                            var correlationColumnJson = correlationForm.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                            //得到列表数据
                            var correlationData = item.Value.ToString().JsonStringToList<Guid>();
                            //判断是否为双向关联
                            if (item.Options.TwoWay.Is)
                            {//查询双向关联字段
                                var twoWay = correlationColumnJson.FirstOrDefault(f => f.Key == item.Options.TwoWay.Key);
                                if (twoWay != null)
                                {
                                    if (correlationData.Any())
                                    {
                                        //为空则替换为空数组
                                        doSqls.Add($"UPDATE {correlationForm.TableName} SET {twoWay.Model} = '[]' WHERE {twoWay.Model} is null");
                                        //移除关联当前
                                        doSqls.Add($"UPDATE {correlationForm.TableName} SET {twoWay.Model} = JSON_REMOVE({twoWay.Model},JSON_UNQUOTE(JSON_SEARCH({twoWay.Model},'one','{input.RowId}'))) WHERE Status=1 and JSON_CONTAINS({twoWay.Model},JSON_ARRAY(\"{input.RowId}\")) ");
                                        //添加关联
                                        doSqls.Add($"UPDATE {correlationForm.TableName} SET {twoWay.Model} = JSON_ARRAY_APPEND({twoWay.Model}, '$', '{input.RowId}') WHERE RowId in ({correlationData.ExpandAndToString().InSql()}) and JSON_CONTAINS({twoWay.Model}, '\"{input.RowId}\"', '$') = 0");
                                    }
                                    else
                                    {
                                        //移除关联当前
                                        doSqls.Add($"UPDATE {correlationForm.TableName} SET {twoWay.Model} = JSON_REMOVE({twoWay.Model},JSON_UNQUOTE(JSON_SEARCH({twoWay.Model},'one','{input.RowId}'))) WHERE Status=1 and JSON_CONTAINS({twoWay.Model},JSON_ARRAY(\"{input.RowId}\")) ");
                                    }
                                }
                            }
                            parameters.Add($"{item.Key}", item.Value);
                        }
                        else
                        {
                            parameters.Add($"{symbol}{item.Key}", item.Value);
                        }
                    }

                    #endregion
                }

                stringBuilder.Append($"UpdateTime={symbol}UpdateTime,");
                stringBuilder.Append($"UpdateUserId={symbol}UpdateUserId,");
                stringBuilder.Append($"UpdateUserName={symbol}UpdateUserName,");
                stringBuilder.Append($"UpdateOrganizationId={symbol}UpdateOrganizationId,");
                stringBuilder.Append($"UpdateOrganizationName={symbol}UpdateOrganizationName,");

                var sql = stringBuilder.ToString().TrimEnd(',') + $" WHERE RowId={symbol}RowId ";
                doSqls.Add(sql);
            }
            return doSqls;
        }

        /// <summary>
        /// 默认参数值
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="input"></param>
        private Dictionary<string, object> SaveParametersDefault(SystemWorkSheetSaveInput input)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("RowId", input.RowId);
            parameters.Add("CreateTime", DateTime.Now);
            parameters.Add("CreateUserId", input.UserId);
            parameters.Add("CreateUserName", input.UserName);
            parameters.Add("CreateOrganizationId", input.OrganizationId);
            parameters.Add("CreateOrganizationName", input.OrganizationName);
            parameters.Add("UpdateTime", DateTime.Now);
            parameters.Add("UpdateUserId", input.UserId);
            parameters.Add("UpdateUserName", input.UserName);
            parameters.Add("UpdateOrganizationId", input.OrganizationId);
            parameters.Add("UpdateOrganizationName", input.OrganizationName);
            return parameters;
        }

        /// <summary>
        /// 判断是否是增加数据
        /// </summary>
        /// <param name="tableName">表</param>
        /// <param name="rowId">主键Id</param>
        /// <returns></returns>
        private bool SaveCheckIsAdd(string tableName, Guid rowId)
        {
            string connectionType = dataBase.ConnectionType;
            string connectionString = dataBase.ConnectionString;
            var selectSql = $"SELECT * FROM {tableName} WHERE RowId='{rowId}'";
            DataTable createDataTable = new DbHelper(connectionString, connectionType).CreateSqlDataTable(selectSql);
            return createDataTable.Rows.Count == 0;
        }

        /// <summary>
        /// 获取数据库所有列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private async Task<List<SystemDataBaseColumnDto>> SaveFindColumns(string tableName)
        {
            return (await _dataBaseRepository.FindDataBaseColumns(new SystemDataBaseTableDto
            {
                ConnectionString = dataBase.ConnectionString,
                ConnectionType = dataBase.ConnectionType,
                Name = tableName
            })).ToList();
        }

        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="columnJson"></param>
        /// <param name="allColumns"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        private List<SystemWorkSheetTableFieldDetailInput> SaveGetFields(string columnJson, IEnumerable<SystemDataBaseColumnDto> allColumns, List<SystemDataBaseSaveBusinessData> values)
        {
            List<SystemWorkSheetTableFieldDetailInput> fields = new List<SystemWorkSheetTableFieldDetailInput>();

            var columns = columnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
            foreach (var column in values)
            {
                if (column.Key.IsNotNullOrEmpty())
                {
                    var columnCheck = columns.FirstOrDefault(f => f.Key == column.Key);
                    if (columnCheck != null)
                    {
                        var colums = allColumns.ToList().Any(w => w.Name.ToUpper() == columnCheck.Model.ToUpper());
                        if (colums)
                        {
                            columnCheck.Value = column.Value;
                            fields.Add(columnCheck);
                        }
                    }
                }
            }
            return fields;
        }

        /// <summary>
        /// 处理文本组合内容
        /// </summary>
        /// <param name="columns">所有配置列</param>
        /// <param name="fields">所有已计算控件值</param>
        /// <param name="allColumns">数据库所有字段</param>
        private void SaveTextCombination(List<SystemWorkSheetTableFieldDetailInput> columns,
            List<SystemDataBaseColumnDto> allColumns,
            ref List<SystemWorkSheetTableFieldDetailInput> fields)
        {
            //处理文本组合字段
            var textControls = columns.Where(item => item.Type == EnumZeroCodeControl.textCombination.ToString()).ToList();
            foreach (var item in textControls)
            {
                if (!item.Options.DefaultValue.IsNullOrEmpty())
                {
                    var textCombination = item.Options.DefaultValue.ToString().ReplaceEditor();
                    var h1Elements = item.Options.DefaultValue.ToString().GetNodes();
                    if (h1Elements != null)
                    {
                        foreach (var element in h1Elements)
                        {
                            var key = HttpUtility.HtmlDecode(element.Id);
                            if (key.IsNotNullOrEmpty())
                            {
                                //解析key
                                string value = string.Empty;
                                //得到值
                                var get = fields.FirstOrDefault(f => f.Key == key);
                                if (get != null && get.Value != null)
                                {
                                    value = get.Value.ToString();
                                }
                                if (key != null && key.IsNotNullOrEmpty())
                                {
                                    var columnCheck = columns.FirstOrDefault(f => f.Key == key);
                                    if (columnCheck != null)
                                    {
                                        if (columnCheck.Type == EnumZeroCodeControl.echeckbox.ToString() ||
                                            columnCheck.Type == EnumZeroCodeControl.eradio.ToString() ||
                                            columnCheck.Type == EnumZeroCodeControl.eselect.ToString())
                                        {
                                            List<SystemDataBaseColumnsOptionOptionsInput> options = new List<SystemDataBaseColumnsOptionOptionsInput>();
                                            //下拉值
                                            foreach (var item1 in columnCheck.Options.Options)
                                            {
                                                if (value.Contains(item1.Value))
                                                {
                                                    options.Add(item1);
                                                }
                                            }
                                            value = options.Select(s => s.Label).ToList().ExpandAndToString("、");
                                        }
                                    }
                                }

                                textCombination = textCombination.Replace("<div class=\"KDefaultSpanTag\">" + element.OuterHtml + "</div>", value.ToString());
                            }
                        }
                    }

                    if (textCombination.IsNotNullOrEmpty())
                    {
                        var columnCheck = columns.FirstOrDefault(f => f.Key == item.Key);
                        if (columnCheck != null)
                        {
                            var colums = allColumns.ToList().Any(w => w.Name.ToUpper() == columnCheck.Model.ToUpper());
                            if (colums)
                            {
                                columnCheck.Value = textCombination;
                                fields.Add(columnCheck);
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 复制数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> Copy(SystemWorkSheetCopyInput input)
        {
            OperateStatus<Guid> operateStatus = new OperateStatus<Guid>();
            string symbol = RepositoryUtil.GetSymbol();
            //所有涉及到的表名称
            List<string> tables = new List<string>();
            //是否具有数据
            var form = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId })).Data;
            try
            {
                //主表所有字段
                string connectionType = dataBase.ConnectionType;
                string connectionString = dataBase.ConnectionString;
                var allColumns = (await _dataBaseRepository.FindDataBaseColumns(new SystemDataBaseTableDto
                {
                    ConnectionString = connectionString,
                    ConnectionType = connectionType,
                    Name = form.TableName
                })).ToList();

                var columns = (allColumns.Where(w => w.Name != "Id" &&
                                             w.Name != "WorkflowStatus" &&
                                             w.Name != "WorkflowSn" &&
                                             w.Name != "WorkflowTitle" &&
                                             w.Name != "RowId" &&
                                             w.Name != "Status" &&
                                             w.Name != "CreateTime" &&
                                             w.Name != "CreateUserId" &&
                                             w.Name != "CreateUserName" &&
                                             w.Name != "CreateOrganizationId" &&
                                             w.Name != "CreateOrganizationName" &&
                                             w.Name != "UpdateTime" &&
                                             w.Name != "UpdateUserId" &&
                                             w.Name != "UpdateUserName" &&
                                             w.Name != "UpdateOrganizationId" &&
                                             w.Name != "UpdateOrganizationName")).Select(s => s.Name).ToList();
                //参数
                //执行Sql
                IList<string> doSqls = new List<string>();

                foreach (var rowId in input.RowId.Split(','))
                {
                    var insertRowId = Guid.NewGuid();
                    string insertSql = $"insert into {form.TableName} ({columns.ExpandAndToString()},RowId,CreateTime,CreateUserId,CreateUserName,CreateOrganizationId,CreateOrganizationName,UpdateTime,UpdateUserId,UpdateUserName,UpdateOrganizationId,UpdateOrganizationName) select {columns.ExpandAndToString()},'{insertRowId}','{DateTime.Now}','{input.UserId}','{input.UserName}','{input.OrganizationId}','{input.OrganizationName}','{DateTime.Now}','{input.UserId}','{input.UserName}','{input.OrganizationId}','{input.OrganizationName}' from {form.TableName} where RowId='{rowId}'";
                    doSqls.Add(insertSql);
                    if (input.Title.IsNotNullOrEmpty())
                    {
                        var updateSql = $"update {form.TableName} set {input.Title}=concat({input.Title},'_复制')  where RowId='{insertRowId}'";
                        doSqls.Add(updateSql);
                    }
                }

                if (doSqls.Any())
                {
                    SystemZeroCodeDataLog systemZeroCodeDataLog = new SystemZeroCodeDataLog();
                    using (IDbConnection connection = GetConnectoin(connectionString, connectionType))
                    {
                        var trans = connection.BeginTransaction();
                        try
                        {
                            var totalInstances = doSqls.Count();
                            int maxAllowedInstancesPerBatch = 30;
                            //总页数
                            int exceededTimes = (int)Math.Ceiling(Convert.ToDouble(totalInstances) / maxAllowedInstancesPerBatch);
                            if (exceededTimes > 1)
                            {
                                for (int i = 0; i <= exceededTimes; i++)
                                {
                                    var skips = i * maxAllowedInstancesPerBatch;

                                    if (skips >= totalInstances)
                                        break;

                                    var items = doSqls.Skip(skips).Take(maxAllowedInstancesPerBatch);
                                    connection.Execute(items.ExpandAndToString(";"), null, trans);
                                }
                            }
                            else
                            {
                                connection.Execute(doSqls.ExpandAndToString(";"), null, trans);
                            }

                            trans.Commit();
                            operateStatus.Code = ResultCode.Success;
                            operateStatus.Msg = Chs.Successful;
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            operateStatus.Msg = "操作失败:" + ex.Message;
                            operateStatus.Code = ResultCode.Error;
                            return operateStatus;
                        }
                    }
                    await _publisher.PublishAsync("ZeroCodeDataLogSaveSubscribe", systemZeroCodeDataLog);
                }
                else
                {
                    operateStatus.Code = ResultCode.Success;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = "操作失败:" + e.Message;
                operateStatus.Code = ResultCode.Error;
                return operateStatus;
            }

            //删除缓存
            if (tables.Any())
            {
                var caches = new List<string>();
                tables = tables.DistinctByExtension(d => d).ToList();
                foreach (var item in tables)
                {
                    StringBuilder cacheString = new StringBuilder();
                    foreach (var name in item.Split('_').Where(w => w != "_"))
                    {
                        cacheString.Append(char.ToUpper(name[0]) + name.Substring(1));
                    }
                    var cache = $"I" + cacheString.ToString() + "Logic_Cache:*";
                    var keys = SearchRedisKeys(cache);
                    if (keys.Any())
                    {
                        RedisHelper.Del(keys.ToArray());
                    }
                }
            }

            return operateStatus;
        }

        /// <summary>
        /// Searchs the redis keys.
        /// </summary>
        /// <returns>The redis keys.</returns>
        /// <param name="pattern">Pattern.</param>
        private string[] SearchRedisKeys(string pattern)
        {
            var keys = new List<string>();

            long nextCursor = 0;
            do
            {
                var scanResult = RedisHelper.Scan(nextCursor, pattern, 500);
                nextCursor = scanResult.Cursor;
                var items = scanResult.Items;
                keys.AddRange(items);
            }
            while (nextCursor != 0);

            var prefix = RedisHelper.Nodes?.Values?.FirstOrDefault()?.Prefix;

            if (!string.IsNullOrWhiteSpace(prefix))
                keys = keys.Select(x => x.Remove(0, prefix.Length)).ToList();

            return keys.Distinct().ToArray();
        }

        /// <summary>
        /// 数据库链接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private IDbConnection GetConnectoin(string connectionString, string connectionType)
        {
            DbConnection connection;
            switch (connectionType)
            {
                case ResourceDataBaseType.Mysql:
                    connection = new MySqlConnection(connectionString);
                    break;
                default:
                    connection = new SqlConnection(connectionString);
                    break;
            }
            if (connection.State != ConnectionState.Open)
                connection.Open();
            return connection;
        }

        /// <summary>
        /// 查询业务数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> Find(SystemWorkSheetFindPagingInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;

                List<SystemWorkSheetTableFieldDetailInput> columnJson = new List<SystemWorkSheetTableFieldDetailInput>();
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //得到需要查询的字段
                    var tables = zeroCode.PublicJson.JsonStringToObject<SystemWorkSheetFindPagingGridInput>();

                    columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();

                    //判断是否分页
                    List<string> fields = new List<string>();

                    //得到需要查询的字段
                    var columns = tables.Field.Data.ToList();
                    //如果是自定义显示列
                    if (input.Status != 9)
                    {
                        columns = columns.Where(w => w.Check).ToList();
                    }
                    foreach (var item in columns)
                    {
                        if (item.Key == ("CreateUser") || item.Key == ("UpdateUser"))
                        {
                            fields.Add(item.Key + "Name");
                            fields.Add(item.Key + "Id");
                        }
                        else
                        {
                            var columnFind = columnJson.FirstOrDefault(f => f.Key == item.Key);
                            if (columnFind != null)
                            {
                                fields.Add(columnFind.Model + " as " + item.Key);
                                //处理关联数据
                                if (columnFind.Type == EnumZeroCodeControl.correlation.ToString() ||
                                    columnFind.Type == EnumZeroCodeControl.dictionary.ToString())
                                {
                                    format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                    {
                                        Type = columnFind.Type,
                                        Key = item.Key,
                                        Field = columnFind
                                    });
                                }

                            }
                            else
                            {
                                fields.Add(item.Key);
                            }
                        }
                    }

                    //是否为删除
                    if (input.Status == 9)
                    {
                        if (!fields.Contains("UpdateTime"))
                        {
                            fields.Add("UpdateTime");
                        }

                        if (!fields.Contains("UpdateUserName"))
                        {
                            fields.Add("UpdateUserName");
                            fields.Add("UpdateUserId");
                        }
                    }
                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }

                    //判断是否有颜色字段
                    if (tables.Color.Field.IsNotNullOrEmpty() && !fields.Any(a => a.Contains(tables.Color.Field)))
                    {
                        fields.Add(tables.Color.Field);
                    }

                    input.Fields = !fields.Any() ? "*" : fields.ExpandAndToString();
                    string sql = input.Sql +
                                  (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                                  (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");

                    //解析查询
                    if (tables.Filter != null)
                    {
                        var convertFilters = ConvertFilters(tables.Filter, columnJson);
                        sql += convertFilters.IsNotNullOrEmpty() ? " and " + convertFilters : "";
                    }
                    StringBuilder orderBy = new StringBuilder();
                    if (input.Sort.IsNotNullOrEmpty())
                    {
                        for (int i = 0; i < input.Sort.Split(',').Length; i++)
                        {
                            var key = input.Sort.Split(',')[i];
                            var orderByType = key.Contains("desc") ? "desc" : "asc";
                            var columnFind = columnJson.FirstOrDefault(f => f.Key == key.Replace(" desc", "").Replace(" asc", ""));

                            if (columnFind != null && columnFind.Type == EnumZeroCodeControl.correlation.ToString())
                            {
                                orderBy.Append($"JSON_EXTRACT({zeroCode.TableName + "." + columnFind.Model}, '$[0]') {orderByType} ,");
                            }
                            else if (columnFind != null && columnFind.Type == EnumZeroCodeControl.otherField.ToString())
                            {
                                //是否为数字
                                if (columnFind.Options.Type == EnumZeroCodeControl.enumber.ToString())
                                {
                                    orderBy.Append($"CONVERT({zeroCode.TableName + "." + columnFind.Model},DECIMAL)  {orderByType} ,");
                                }
                                else
                                {
                                    orderBy.Append($"{zeroCode.TableName + "." + key},");
                                }
                            }
                            else if (columnFind != null &&
                                (columnFind.Type == EnumZeroCodeControl.enumber.ToString()
                                || columnFind.Type == EnumZeroCodeControl.formulaNumber.ToString()))
                            {
                                orderBy.Append($"CONVERT({zeroCode.TableName + "." + columnFind.Model},DECIMAL) {orderByType} ,");
                            }

                            else
                            {
                                var userKey = key.Replace(" desc", "").Replace(" asc", "");
                                if (userKey == ("CreateUser") || userKey == ("UpdateUser"))
                                {
                                    fields.Add(userKey + "Id");
                                }
                                orderBy.Append($"{zeroCode.TableName + "." + key},");
                            }
                        }
                    }
                    else
                    {
                        //若排序字段为空则取最后一个字段作为排序字段
                        if (fields.Any())
                        {
                            orderBy.Append(zeroCode.TableName + "." + fields[fields.Count() - 1] + "  Desc ");
                        }
                        else
                        {
                            orderBy.Append(" Id desc ");
                        }
                    }

                    var currentPage = input.Current; //当前页号
                    var pageSize = input.Size; //每页记录数
                    var lower = ((currentPage - 1) * pageSize) + 1; //记录起点
                    var upper = currentPage * pageSize; //记录终点

                    var querySql = $"select {input.Fields},@rowNumber, @recordCount from {zeroCode.TableName} @where and Status={input.Status}  ";
                    var querySqlQueryMultiple = input.IsReport ?
                                      $@"select * from ({querySql}) seq "
                                      : $@"select * from ({querySql}) seq where seq.row_num between {lower} and {upper}";

                    var orderString = $" {orderBy.ToString().TrimEnd(',')}";
                    var recordCount = querySqlQueryMultiple.Split("@recordCount");
                    var selectSql = recordCount[0].Trim().TrimEnd(',');
                    querySqlQueryMultiple = selectSql + " " + recordCount[1];
                    querySqlQueryMultiple = querySqlQueryMultiple.Replace("@rowNumber", " row_number() over (order by @orderBy) as row_num ")
                        .Replace("@orderBy", orderString)
                        .Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));

                    var querySqlRecordCount = querySql.Split("@recordCount");
                    var recordCountSql = querySql.Replace(querySqlRecordCount[0], ";select count(*) ").Replace("@recordCount", "  ").Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));
                    //总数量
                    querySqlQueryMultiple += recordCountSql;
                    var queryMulti = fix.Db.Connection.QueryMultiple(querySqlQueryMultiple);
                    data = queryMulti.Read<dynamic>().ToList();
                    v.count = queryMulti.Read<long>().Sum();
                }

                //数据再次处理
                if (data.Any())
                {
                    #region 处理用户数据
                    if (input.Fields.Contains("CreateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "CreateUser"
                        });
                    }
                    if (input.Fields.Contains("UpdateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "UpdateUser"
                        });
                    }

                    //值对应的用户Id集合
                    List<string> userIds = new List<string>();
                    List<SystemUserHeaderOutput> user = new List<SystemUserHeaderOutput>();
                    if (format.Any(a => a.Type == "User"))
                    {
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                if (formatUsers)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().Split(',');
                                        foreach (var id in ids)
                                        {
                                            if (!userIds.Any(a => a == id))
                                            {
                                                userIds.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        if (userIds.Any())
                        {
                            //查询用户头像
                            var sql = $"select HeadImage,UserId from system_userinfo where UserId in ({userIds.ExpandAndToString().InSql()})";
                            using (var fix = new SqlDatabaseFixture())
                            {
                                user = (await fix.Db.Connection.QueryAsync<SystemUserHeaderOutput>(sql)).ToList();
                            }
                        }
                    }

                    //是否具有需要替换的Id,或者需要掩码
                    if (userIds.Any())
                    {
                        List<dynamic> tmp = new List<dynamic>();
                        foreach (var item in data)
                        {
                            dynamic info = new ExpandoObject();
                            var dic = (IDictionary<string, object>)info;
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                object dvalue = dd.Value;

                                if (dvalue != null)
                                {
                                    //头像处理
                                    var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                    if (formatUsers)
                                    {
                                        var userIdSplit = dvalue.ToString().Split(',');
                                        List<string> userIdHeader = new List<string>();
                                        foreach (var userId in userIdSplit)
                                        {
                                            userIdHeader.Add(user.FirstOrDefault(f => f.UserId.ToString() == userId)?.HeadImage);
                                        }
                                        dic.Add(dkey + "Header", userIdHeader.ExpandAndToString());
                                    }
                                }
                                dic.Add(dkey, dvalue);
                            }
                            tmp.Add(info);
                        }
                        data = tmp;
                    }
                    #endregion

                    #region 关联数据
                    var correlations = format.Where(w => w.Type == EnumZeroCodeControl.correlation.ToString());
                    foreach (var correlation in correlations)
                    {
                        //判断是否是多条
                        var columnFind = columnJson.FirstOrDefault(f => f.Key == correlation.Key);
                        if (columnFind.Options.ShowType != EnumZeroCodeControlCorrelationShowType.grid.ToString())
                        {
                            List<string> rowId = new List<string>();
                            foreach (var item in data)
                            {
                                foreach (var dd in item)
                                {
                                    string dkey = dd.Key;
                                    if (correlation.Key == dkey)
                                    {
                                        object dvalue = dd.Value;
                                        if (dvalue != null)
                                        {
                                            var ids = dvalue.ToString().JsonStringToList<string>();
                                            foreach (var id in ids)
                                            {
                                                if (!rowId.Any(a => a == id))
                                                {
                                                    rowId.Add(id);
                                                }
                                            }
                                        }
                                    }
                                }
                            }

                            if (rowId.Any())
                            {
                                //得到配置信息
                                var correlationFind = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)correlation.Field.Options.ZeroCodeId }, true)).Data;
                                //得到标题
                                var config = correlationFind.PublicJson.JsonStringToObject<SystemZeroCodePublicJsonWorkSheetOutput>();
                                //转换列
                                var columnCorrelationJson = correlationFind.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();

                                var title = columnCorrelationJson.FirstOrDefault(f => f.Key == config.Config.Title);
                                if (title != null)
                                {
                                    var sql = $"select RowId,{title.Model} as Title from {correlationFind.TableName} where RowId in ({rowId.ExpandAndToString().InSql()})";
                                    List<SystemZeroCodeCorrelationOutput> correlationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                    using (var fix = new SqlDatabaseFixture())
                                    {
                                        correlationOutputs = (await fix.Db.Connection.QueryAsync<SystemZeroCodeCorrelationOutput>(sql)).ToList();
                                    }
                                    List<dynamic> tmp = new List<dynamic>();
                                    foreach (var item in data)
                                    {
                                        dynamic info = new ExpandoObject();
                                        var dic = (IDictionary<string, object>)info;
                                        foreach (var dd in item)
                                        {
                                            string dkey = dd.Key;
                                            object dvalue = dd.Value;

                                            if (correlation.Key == dkey && dvalue != null)
                                            {
                                                //数据处理
                                                var ids = dvalue.ToString().JsonStringToList<Guid>();
                                                List<SystemZeroCodeCorrelationOutput> codeCorrelationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                                foreach (var id in ids)
                                                {
                                                    var find = correlationOutputs.FirstOrDefault(f => f.RowId == id);
                                                    codeCorrelationOutputs.Add(find);
                                                }
                                                dic.Add(dkey, JsonConvert.SerializeObject(codeCorrelationOutputs));
                                            }
                                            else
                                            {
                                                dic.Add(dkey, dvalue);
                                            }
                                        }
                                        tmp.Add(info);
                                    }
                                    data = tmp;

                                }
                            }
                        }
                    }

                    #endregion

                    #region 字段
                    var dictionarys = format.Where(w => w.Type == EnumZeroCodeControl.dictionary.ToString());
                    foreach (var dictionary in dictionarys)
                    {
                        List<string> rowId = new List<string>();
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                if (dictionary.Key == dkey)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().JsonStringToList<string>();
                                        foreach (var id in ids)
                                        {
                                            if (!rowId.Any(a => a == id))
                                            {
                                                rowId.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        if (rowId.Any())
                        {
                            //查询用户头像
                            var sql = $"select DictionaryId RowId,Name as Title,Color from System_Dictionary where DictionaryId in ({rowId.ExpandAndToString().InSql()})";
                            List<SystemZeroCodeCorrelationOutput> dictionaryOutputs = new List<SystemZeroCodeCorrelationOutput>();
                            using (var fix = new SqlDatabaseFixture())
                            {
                                dictionaryOutputs = (await fix.Db.Connection.QueryAsync<SystemZeroCodeCorrelationOutput>(sql)).ToList();
                            }

                            List<dynamic> tmp = new List<dynamic>();
                            foreach (var item in data)
                            {
                                dynamic info = new ExpandoObject();
                                var dic = (IDictionary<string, object>)info;
                                foreach (var dd in item)
                                {
                                    string dkey = dd.Key;
                                    object dvalue = dd.Value;

                                    if (dictionary.Key == dkey && dvalue != null)
                                    {
                                        //数据处理
                                        var ids = dvalue.ToString().JsonStringToList<Guid>();
                                        List<SystemZeroCodeCorrelationOutput> codeCorrelationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                        foreach (var id in ids)
                                        {
                                            var find = dictionaryOutputs.FirstOrDefault(f => f.RowId == id);
                                            codeCorrelationOutputs.Add(find);
                                        }
                                        dic.Add(dkey, JsonConvert.SerializeObject(codeCorrelationOutputs));
                                    }
                                    else
                                    {
                                        dic.Add(dkey, dvalue);
                                    }
                                }
                                tmp.Add(info);
                            }
                            data = tmp;
                        }
                    }

                    #endregion
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }



        /// <summary>
        /// 查询业务数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> CheckOnly(SystemZeroCodeOnlyInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus();
            try
            {
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var columns = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;

                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    var column = columns.FirstOrDefault(f => f.Key == input.Key);
                    string selectSql = $"SELECT Id FROM {zeroCode.TableName} where 1=1 and {column.Model}='{input.Value}'";
                    if (input.RowId.HasValue)
                    {
                        selectSql += $" and RowId!='{input.RowId}'";
                    }
                    var data = (await fix.Db.Connection.QueryAsync<dynamic>(selectSql)).ToList();
                    if (data.ToList().Count > 0)
                    {
                        return OperateStatus.Error("已存在相同值");
                    }
                }

                return OperateStatus.Success();
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 获取关联表业务数据默认值
        /// 新增时关联字段默认值获取
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindCorrelationDefault(SystemWorkSheetFindCorrelationDefaultFieldInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;
                List<string> columns = new List<string>();
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //得到需要查询的工作表
                    var columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    var keyFind = columnJson.FirstOrDefault(f => f.Key == input.Key);
                    columns = keyFind.Options.Columns;
                    //判断是否分页
                    List<string> fields = new List<string>();
                    var correlationZeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)keyFind.Options.ZeroCodeId }, true)).Data;
                    if (zeroCode.TableName.IsNullOrEmpty())
                    {
                        operateStatus.Msg = "请配置数据源";
                        return operateStatus;
                    }
                    var correlationColumnJson = correlationZeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    //如果是自定义显示列
                    foreach (var item in columns.Distinct())
                    {
                        if (item == ("CreateUser") || item == ("UpdateUser"))
                        {
                            fields.Add(item + "Name");
                            fields.Add(item + "Id");
                        }
                        else
                        {
                            var columnFind = correlationColumnJson.FirstOrDefault(f => f.Key == item);
                            if (columnFind != null)
                            {
                                fields.Add(columnFind.Model + " as " + item);

                                //处理关联数据
                                if (columnFind.Type == EnumZeroCodeControl.correlation.ToString() ||
                                    columnFind.Type == EnumZeroCodeControl.dictionary.ToString())
                                {
                                    format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                    {
                                        Type = columnFind.Type,
                                        Key = item,
                                        Field = columnFind
                                    });
                                }
                            }
                            else
                            {
                                fields.Add(item);
                            }
                        }
                    }

                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }
                    //得到标题
                    var config = correlationZeroCode.PublicJson.JsonStringToObject<SystemZeroCodePublicJsonWorkSheetOutput>();
                    var title = correlationColumnJson.FirstOrDefault(f => f.Key == config.Config.Title);
                    if (title != null)
                    {
                        var key = title.Model + " as " + title.Key;
                        if (!fields.Contains(key))
                        {
                            fields.Add(key);
                        }
                    }
                    //判断默认值类型
                    var querySql = $"select {fields.ExpandAndToString()} from {correlationZeroCode.TableName}  WHERE Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE({keyFind.Model},'$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}') ";
                    var queryMulti = fix.Db.Connection.QueryMultiple(querySql);
                    data = queryMulti.Read<dynamic>().ToList();
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 获取关联表业务数据默认值
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindCorrelationDefaultField(SystemWorkSheetFindCorrelationDefaultFieldInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;
                List<string> columns = new List<string>();
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //得到需要查询的工作表
                    var columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    var keyFind = columnJson.FirstOrDefault(f => f.Key == input.Key);
                    columns = keyFind.Options.Columns;
                    //判断是否分页
                    List<string> fields = new List<string>();
                    var correlationZeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)keyFind.Options.ZeroCodeId }, true)).Data;
                    if (zeroCode.TableName.IsNullOrEmpty())
                    {
                        operateStatus.Msg = "请配置数据源";
                        return operateStatus;
                    }
                    var correlationColumnJson = correlationZeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    //如果是自定义显示列
                    foreach (var item in columns.Distinct())
                    {
                        if (item == ("CreateUser") || item == ("UpdateUser"))
                        {
                            fields.Add(item + "Name");
                            fields.Add(item + "Id");
                        }
                        else
                        {
                            var columnFind = correlationColumnJson.FirstOrDefault(f => f.Key == item);
                            if (columnFind != null)
                            {
                                fields.Add(columnFind.Model + " as " + item);

                                //处理关联数据
                                if (columnFind.Type == EnumZeroCodeControl.correlation.ToString() ||
                                    columnFind.Type == EnumZeroCodeControl.dictionary.ToString())
                                {
                                    format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                    {
                                        Type = columnFind.Type,
                                        Key = item,
                                        Field = columnFind
                                    });
                                }
                            }
                            else
                            {
                                fields.Add(item);
                            }
                        }
                    }

                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }

                    //判断是多选还是单选
                    if (keyFind.Options.DefaultValueConfig.Type == EnumWorkSheetControlDefaultValueConfigType.field.ToString()
                        && keyFind.Options.DefaultValueConfig.Field.Any())
                    {
                        //判断默认值类型
                        var querySql = $"select {fields.ExpandAndToString()} from {correlationZeroCode.TableName}  WHERE Status=1 and RowId in ({keyFind.Options.DefaultValueConfig.Field.Select(s => s.Key).ExpandAndToString().InSql()})";
                        var queryMulti = fix.Db.Connection.QueryMultiple(querySql);
                        data = queryMulti.Read<dynamic>().ToList();
                    }
                    //工作表
                    else if (keyFind.Options.DefaultValueConfig.Type == EnumWorkSheetControlDefaultValueConfigType.worksheet.ToString())
                    {
                        //判断是多选还是单选
                        var number = 2;
                        if (keyFind.Options.Type == EnumZeroCodeControlCorrelationType.multiple.ToString())
                        {
                            number = keyFind.Options.DefaultValueConfig.WorkSheet.Number;
                        }
                        //判断默认值类型
                        var querySql = $"select {fields.ExpandAndToString()} from {correlationZeroCode.TableName}  WHERE Status=1  ";
                        var filterSql = SearchFilterUtil.ConvertFilters(keyFind.Options.DefaultValueConfig.WorkSheet.Filters);
                        querySql += (filterSql.IsNotNullOrEmpty() ? " and " + filterSql : "") + $" limit {number}";
                        var queryMulti = fix.Db.Connection.QueryMultiple(querySql);
                        data = queryMulti.Read<dynamic>().ToList();
                        if (keyFind.Options.Type == EnumZeroCodeControlCorrelationType.single.ToString()
                        && data.Count > 1)
                        {
                            if (keyFind.Options.DefaultValueConfig.WorkSheet.MultipleType == EnumZeroCodeControlDefaultValueConfigWorkSheetMultipleType.no)
                            {
                                data = new List<dynamic>();
                            }
                            else
                            {
                                data = data.FirstOrDefault();
                            }
                        }
                    }
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 获取关联表业务数据默认值
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindCorrelationDefaultWorkSheet(SystemWorkSheetFindCorrelationDefaultWorkSheetInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //得到需要查询的工作表
                    var columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    var keyFind = columnJson.FirstOrDefault(f => f.Key == input.Key);
                    var item = keyFind.Options.DefaultValueConfig.WorkSheet.SetKey;
                    //判断是否分页
                    List<string> fields = new List<string>();
                    var correlationZeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)keyFind.Options.DefaultValueConfig.WorkSheet.ZeroCodeId }, true)).Data;
                    if (correlationZeroCode.TableName.IsNullOrEmpty())
                    {
                        operateStatus.Msg = "请配置数据源";
                        return operateStatus;
                    }
                    var correlationColumnJson = correlationZeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();

                    if (item == ("CreateUser") || item == ("UpdateUser"))
                    {
                        fields.Add(item + "Name");
                        fields.Add(item + "Id");
                    }
                    else
                    {
                        var columnFind = correlationColumnJson.FirstOrDefault(f => f.Key == item);
                        if (columnFind != null)
                        {
                            fields.Add(columnFind.Model + " as " + columnFind.Key);

                            //处理关联数据
                            if (columnFind.Type == EnumZeroCodeControl.correlation.ToString() ||
                                columnFind.Type == EnumZeroCodeControl.dictionary.ToString())
                            {
                                format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                {
                                    Type = columnFind.Type,
                                    Key = item,
                                    Field = columnFind
                                });
                            }
                        }
                    }

                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }
                    //判断是多选还是单选
                    var number = 1;
                    if (keyFind.Options.Type == EnumZeroCodeControlCorrelationType.multiple.ToString())
                    {
                        number = keyFind.Options.DefaultValueConfig.WorkSheet.Number;
                    }

                    List<SystemDataBaseSaveBusinessData> values = new List<SystemDataBaseSaveBusinessData>();
                    if (input.Values.IsNotNullOrEmpty())
                    {
                        values = input.Values.JsonStringToList<SystemDataBaseSaveBusinessData>();
                    }
                    //判断默认值类型
                    var querySql = $"select {fields.ExpandAndToString()} from {correlationZeroCode.TableName}  WHERE Status=1  ";
                    Dictionary<string, bool> checkResultAllNull = new Dictionary<string, bool>();
                    //检测是否关联字段都为空
                    CheckResultAllNull(
                        keyFind.Options.DefaultValueConfig.WorkSheet.Filters,
                        correlationColumnJson,
                        values,
                        keyFind.Options.DefaultValueConfig.WorkSheet, checkResultAllNull);

                    //判断是否有空的
                    if (checkResultAllNull.Count(c => c.Value == true) > 0)
                    {
                        //3,忽略此条件（当全部忽略时，返回无结果）
                        if (keyFind.Options.DefaultValueConfig.WorkSheet.DynamicValueEmpty == 1
                            || (keyFind.Options.DefaultValueConfig.WorkSheet.DynamicValueEmpty == 3 &&
                            checkResultAllNull.Count(c => c.Value == true) == checkResultAllNull.Count()))
                        {
                            v.data = null;
                            return OperateStatus<dynamic>.Success(v);
                        }
                    }
                    var convertFilters = ConvertFiltersWorkSheet(
                        keyFind.Options.DefaultValueConfig.WorkSheet.Filters,
                        correlationColumnJson,
                        values,
                        keyFind.Options.DefaultValueConfig.WorkSheet);

                    querySql += (convertFilters.IsNotNullOrEmpty() ? " and " + convertFilters : "") + $" limit {number}";
                    var queryMulti = fix.Db.Connection.QueryMultiple(querySql);
                    data = queryMulti.Read<dynamic>().ToList();
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }


        /// <summary>
        /// 查询关联记录业务表数据
        /// 主要用于关联字段选择关联数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindCorrelationFilter(SystemWorkSheetFindCorrelationFilterInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                //当前工作表配置
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;
                List<string> columns = new List<string>();
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //关联表配置
                    var columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    var correlationFind = columnJson.FirstOrDefault(f => f.Key == input.Key);
                    var correlationZeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)correlationFind.Options.ZeroCodeId }, true)).Data;
                    if (zeroCode.TableName.IsNullOrEmpty())
                    {
                        operateStatus.Msg = "请配置数据源";
                        return operateStatus;
                    }
                    columns = correlationFind.Options.Columns;
                    List<string> fields = new List<string>();
                    var correlationColumnJson = correlationZeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    //如果是自定义显示列
                    foreach (var item in columns.Distinct())
                    {
                        if (item == ("CreateUser") || item == ("UpdateUser"))
                        {
                            fields.Add(item + "Name");
                            fields.Add(item + "Id");
                        }
                        else
                        {

                            var columnFind = correlationColumnJson.FirstOrDefault(f => f.Key == item);
                            if (columnFind != null)
                            {
                                fields.Add(columnFind.Model + " as " + item);

                                //处理关联数据
                                if (columnFind.Type == EnumZeroCodeControl.correlation.ToString() ||
                                    columnFind.Type == EnumZeroCodeControl.dictionary.ToString())
                                {
                                    format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                    {
                                        Type = columnFind.Type,
                                        Key = item,
                                        Field = columnFind
                                    });
                                }
                            }
                            else
                            {
                                fields.Add(item);
                            }
                        }
                    }

                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }

                    //得到标题
                    var config = correlationZeroCode.PublicJson.JsonStringToObject<SystemZeroCodePublicJsonWorkSheetOutput>();
                    var title = correlationColumnJson.FirstOrDefault(f => f.Key == config.Config.Title);
                    if (title != null)
                    {
                        var key = title.Model + " as " + title.Key;
                        if (!fields.Contains(key))
                        {
                            fields.Add(key);
                        }
                    }
                    string sql = input.Sql +
                              (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                              (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");
                    StringBuilder orderBy = new StringBuilder();
                    if (input.Sort.IsNotNullOrEmpty())
                    {
                        for (int i = 0; i < input.Sort.Split(',').Length; i++)
                        {
                            orderBy.Append($"{correlationZeroCode.TableName + "." + input.Sort.Split(',')[i]} ,");
                        }
                    }
                    else
                    {
                        orderBy.Append(" Id asc ");
                    }

                    var currentPage = input.Current; //当前页号
                    var pageSize = input.Size; //每页记录数
                    var lower = ((currentPage - 1) * pageSize) + 1; //记录起点
                    var upper = currentPage * pageSize; //记录终点

                    var querySql = $"select {fields.ExpandAndToString()},@rowNumber, @recordCount from {correlationZeroCode.TableName} @where and Status=1  ";
                    var querySqlQueryMultiple = input.IsReport ?
                                      $@"select * from ({querySql}) seq "
                                      : $@"select * from ({querySql}) seq where seq.row_num between {lower} and {upper}";

                    var orderString = $" {orderBy.ToString().TrimEnd(',')}";
                    var recordCount = querySqlQueryMultiple.Split("@recordCount");
                    var selectSql = recordCount[0].Trim().TrimEnd(',');
                    querySqlQueryMultiple = selectSql + " " + recordCount[1];
                    querySqlQueryMultiple = querySqlQueryMultiple.Replace("@rowNumber", " row_number() over (order by @orderBy) as row_num ")
                        .Replace("@orderBy", orderString)
                        .Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));

                    var querySqlRecordCount = querySql.Split("@recordCount");
                    var recordCountSql = querySql.Replace(querySqlRecordCount[0], ";select count(*) ").Replace("@recordCount", "  ").Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));
                    //总数量
                    querySqlQueryMultiple += recordCountSql;
                    var queryMulti = fix.Db.Connection.QueryMultiple(querySqlQueryMultiple);
                    data = queryMulti.Read<dynamic>().ToList();
                    v.count = queryMulti.Read<long>().Sum();
                }

                //数据再次处理
                if (data.Any())
                {
                    if (columns.Contains("CreateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "CreateUser"
                        });
                    }
                    if (columns.Contains("UpdateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "UpdateUser"
                        });
                    }

                    //值对应的用户Id集合
                    List<string> userIds = new List<string>();
                    List<SystemUserHeaderOutput> user = new List<SystemUserHeaderOutput>();
                    if (format.Any(a => a.Type == "User"))
                    {
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                if (formatUsers)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().Split(',');
                                        foreach (var id in ids)
                                        {
                                            if (!userIds.Any(a => a == id))
                                            {
                                                userIds.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        if (userIds.Any())
                        {
                            //查询用户头像
                            var sql = $"select HeadImage,UserId from system_userinfo where UserId in ({userIds.ExpandAndToString().InSql()})";
                            using (var fix = new SqlDatabaseFixture())
                            {
                                user = (await fix.Db.Connection.QueryAsync<SystemUserHeaderOutput>(sql)).ToList();
                            }
                        }
                    }

                    //是否具有需要替换的Id,或者需要掩码
                    if (userIds.Any())
                    {
                        List<dynamic> tmp = new List<dynamic>();
                        foreach (var item in data)
                        {
                            dynamic info = new ExpandoObject();
                            var dic = (IDictionary<string, object>)info;
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                object dvalue = dd.Value;

                                if (dvalue != null)
                                {
                                    //头像处理
                                    var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                    if (formatUsers)
                                    {
                                        var userIdSplit = dvalue.ToString().Split(',');
                                        List<string> userIdHeader = new List<string>();
                                        foreach (var userId in userIdSplit)
                                        {
                                            userIdHeader.Add(user.FirstOrDefault(f => f.UserId.ToString() == userId)?.HeadImage);
                                        }
                                        dic.Add(dkey + "Header", userIdHeader.ExpandAndToString());
                                    }
                                }
                                dic.Add(dkey, dvalue);
                            }
                            tmp.Add(info);
                        }
                        data = tmp;
                    }

                    #region 关联数据
                    var correlations = format.Where(w => w.Type == EnumZeroCodeControl.correlation.ToString());
                    foreach (var correlation in correlations)
                    {
                        List<string> rowId = new List<string>();
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                if (correlation.Key == dkey)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().JsonStringToList<string>();
                                        foreach (var id in ids)
                                        {
                                            if (!rowId.Any(a => a == id))
                                            {
                                                rowId.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        if (rowId.Any())
                        {
                            //得到配置信息
                            var correlationFind = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)correlation.Field.Options.ZeroCodeId }, true)).Data;
                            //得到标题
                            var config = correlationFind.PublicJson.JsonStringToObject<SystemZeroCodePublicJsonWorkSheetOutput>();
                            //转换列
                            var columnJson = correlationFind.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                            var title = columnJson.FirstOrDefault(f => f.Key == config.Config.Title);
                            if (title != null)
                            {
                                var sql = $"select RowId,{title.Model} as Title from {correlationFind.TableName} where RowId in ({rowId.ExpandAndToString().InSql()})";
                                List<SystemZeroCodeCorrelationOutput> correlationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                using (var fix = new SqlDatabaseFixture())
                                {
                                    correlationOutputs = (await fix.Db.Connection.QueryAsync<SystemZeroCodeCorrelationOutput>(sql)).ToList();
                                }
                                List<dynamic> tmp = new List<dynamic>();
                                foreach (var item in data)
                                {
                                    dynamic info = new ExpandoObject();
                                    var dic = (IDictionary<string, object>)info;
                                    foreach (var dd in item)
                                    {
                                        string dkey = dd.Key;
                                        object dvalue = dd.Value;

                                        if (correlation.Key == dkey && dvalue != null)
                                        {
                                            //数据处理
                                            var ids = dvalue.ToString().JsonStringToList<Guid>();
                                            List<SystemZeroCodeCorrelationOutput> codeCorrelationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                            foreach (var id in ids)
                                            {
                                                var find = correlationOutputs.FirstOrDefault(f => f.RowId == id);
                                                codeCorrelationOutputs.Add(find);
                                            }
                                            dic.Add(dkey, JsonConvert.SerializeObject(codeCorrelationOutputs));
                                        }
                                        else
                                        {
                                            dic.Add(dkey, dvalue);
                                        }
                                    }
                                    tmp.Add(info);
                                }
                                data = tmp;

                            }
                        }
                    }

                    #endregion
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 查询关联记录业务表数据
        /// 主要用于关联字段选择关联数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindCorrelationFilterAll(SystemWorkSheetFindCorrelationFilterAllInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                //当前工作表配置
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;
                List<string> columns = new List<string>();
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //关联表配置
                    var columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    var columnShow = input.Columns.Split(',').ToList();
                    List<string> fields = new List<string>();
                    //如果是自定义显示列
                    foreach (var item in columnShow)
                    {
                        var check = columnJson.FirstOrDefault(s => s.Key == item);
                        if (check != null)
                        {
                            fields.Add(check.Model + " as " + check.Key);
                            //处理关联数据
                            if (check.Type == EnumZeroCodeControl.correlation.ToString() ||
                                check.Type == EnumZeroCodeControl.dictionary.ToString())
                            {
                                format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                {
                                    Type = check.Type,
                                    Key = check.Key,
                                    Field = check
                                });
                            }
                        }
                        else
                        {
                            switch (item)
                            {
                                case "CreateTime":
                                case "UpdateTime":
                                    fields.Add(item);
                                    break;
                                case "CreateUser":
                                case "UpdateUser":
                                    fields.Add(item + "Id");
                                    break;
                            }
                        }
                    }

                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }

                    string sql = input.Sql +
                                  (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                                  (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");
                    StringBuilder orderBy = new StringBuilder();

                    orderBy.Append(" Id desc ");

                    var currentPage = input.Current; //当前页号
                    var pageSize = input.Size; //每页记录数
                    var lower = ((currentPage - 1) * pageSize) + 1; //记录起点
                    var upper = currentPage * pageSize; //记录终点

                    var querySql = $"select {fields.ExpandAndToString()},@rowNumber, @recordCount from {zeroCode.TableName} @where and Status=1  ";
                    var querySqlQueryMultiple = input.IsReport ?
                                      $@"select * from ({querySql}) seq "
                                      : $@"select * from ({querySql}) seq where seq.row_num between {lower} and {upper}";

                    var orderString = $" {orderBy.ToString().TrimEnd(',')}";
                    var recordCount = querySqlQueryMultiple.Split("@recordCount");
                    var selectSql = recordCount[0].Trim().TrimEnd(',');
                    querySqlQueryMultiple = selectSql + " " + recordCount[1];
                    querySqlQueryMultiple = querySqlQueryMultiple.Replace("@rowNumber", " row_number() over (order by @orderBy) as row_num ")
                        .Replace("@orderBy", orderString)
                        .Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));

                    var querySqlRecordCount = querySql.Split("@recordCount");
                    var recordCountSql = querySql.Replace(querySqlRecordCount[0], ";select count(*) ").Replace("@recordCount", "  ").Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));
                    //总数量
                    querySqlQueryMultiple += recordCountSql;
                    var queryMulti = fix.Db.Connection.QueryMultiple(querySqlQueryMultiple);
                    data = queryMulti.Read<dynamic>().ToList();
                    v.count = queryMulti.Read<long>().Sum();
                }

                //数据再次处理
                if (data.Any())
                {
                    if (columns.Contains("CreateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "CreateUser"
                        });
                    }
                    if (columns.Contains("UpdateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "UpdateUser"
                        });
                    }

                    //值对应的用户Id集合
                    List<string> userIds = new List<string>();
                    List<SystemUserHeaderOutput> user = new List<SystemUserHeaderOutput>();
                    if (format.Any(a => a.Type == "User"))
                    {
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                if (formatUsers)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().Split(',');
                                        foreach (var id in ids)
                                        {
                                            if (!userIds.Any(a => a == id))
                                            {
                                                userIds.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        if (userIds.Any())
                        {
                            //查询用户头像
                            var sql = $"select HeadImage,UserId from system_userinfo where UserId in ({userIds.ExpandAndToString().InSql()})";
                            using (var fix = new SqlDatabaseFixture())
                            {
                                user = (await fix.Db.Connection.QueryAsync<SystemUserHeaderOutput>(sql)).ToList();
                            }
                        }
                    }

                    //是否具有需要替换的Id,或者需要掩码
                    if (userIds.Any())
                    {
                        List<dynamic> tmp = new List<dynamic>();
                        foreach (var item in data)
                        {
                            dynamic info = new ExpandoObject();
                            var dic = (IDictionary<string, object>)info;
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                object dvalue = dd.Value;

                                if (dvalue != null)
                                {
                                    //头像处理
                                    var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                    if (formatUsers)
                                    {
                                        var userIdSplit = dvalue.ToString().Split(',');
                                        List<string> userIdHeader = new List<string>();
                                        foreach (var userId in userIdSplit)
                                        {
                                            userIdHeader.Add(user.FirstOrDefault(f => f.UserId.ToString() == userId)?.HeadImage);
                                        }
                                        dic.Add(dkey + "Header", userIdHeader.ExpandAndToString());
                                    }
                                }
                                dic.Add(dkey, dvalue);
                            }
                            tmp.Add(info);
                        }
                        data = tmp;
                    }

                    #region 关联数据
                    var correlations = format.Where(w => w.Type == EnumZeroCodeControl.correlation.ToString());
                    foreach (var correlation in correlations)
                    {
                        List<string> rowId = new List<string>();
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                if (correlation.Key == dkey)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().JsonStringToList<string>();
                                        foreach (var id in ids)
                                        {
                                            if (!rowId.Any(a => a == id))
                                            {
                                                rowId.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        if (rowId.Any())
                        {
                            //得到配置信息
                            var correlationFind = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)correlation.Field.Options.ZeroCodeId }, true)).Data;
                            //得到标题
                            var config = correlationFind.PublicJson.JsonStringToObject<SystemZeroCodePublicJsonWorkSheetOutput>();
                            //转换列
                            var columnJson = correlationFind.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                            var title = columnJson.FirstOrDefault(f => f.Key == config.Config.Title);
                            if (title != null)
                            {
                                var sql = $"select RowId,{title.Model} as Title from {correlationFind.TableName} where RowId in ({rowId.ExpandAndToString().InSql()})";
                                List<SystemZeroCodeCorrelationOutput> correlationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                using (var fix = new SqlDatabaseFixture())
                                {
                                    correlationOutputs = (await fix.Db.Connection.QueryAsync<SystemZeroCodeCorrelationOutput>(sql)).ToList();
                                }
                                List<dynamic> tmp = new List<dynamic>();
                                foreach (var item in data)
                                {
                                    dynamic info = new ExpandoObject();
                                    var dic = (IDictionary<string, object>)info;
                                    foreach (var dd in item)
                                    {
                                        string dkey = dd.Key;
                                        object dvalue = dd.Value;

                                        if (correlation.Key == dkey && dvalue != null)
                                        {
                                            //数据处理
                                            var ids = dvalue.ToString().JsonStringToList<Guid>();
                                            List<SystemZeroCodeCorrelationOutput> codeCorrelationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                            foreach (var id in ids)
                                            {
                                                var find = correlationOutputs.FirstOrDefault(f => f.RowId == id);
                                                codeCorrelationOutputs.Add(find);
                                            }
                                            dic.Add(dkey, JsonConvert.SerializeObject(codeCorrelationOutputs));
                                        }
                                        else
                                        {
                                            dic.Add(dkey, dvalue);
                                        }
                                    }
                                    tmp.Add(info);
                                }
                                data = tmp;

                            }
                        }
                    }

                    #endregion
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 编辑时查询关联记录业务表数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindCorrelationRows(SystemWorkSheetFindCorrelationRowsInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                //当前工作表配置
                var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                if (zeroCode.TableName.IsNullOrEmpty())
                {
                    operateStatus.Msg = "请配置数据源";
                    return operateStatus;
                }
                //先判断当前Id是否具有值
                SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();
                sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;

                var format = new List<SystemWorkSheetFindPagingGridFieldDataInput>();
                List<dynamic> data = new List<dynamic>();
                dynamic v = new ExpandoObject();

                List<string> columns = new List<string>();
                using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                {
                    //关联表配置
                    var columnJson = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    var correlationFind = columnJson.FirstOrDefault(f => f.Key == input.Key);
                    var correlationZeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)correlationFind.Options.ZeroCodeId }, true)).Data;
                    if (zeroCode.TableName.IsNullOrEmpty())
                    {
                        operateStatus.Msg = "请配置数据源";
                        return operateStatus;
                    }
                    columns = correlationFind.Options.Columns;
                    List<string> fields = new List<string>();
                    var correlationColumnJson = correlationZeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                    //如果是自定义显示列
                    foreach (var item in columns.Distinct())
                    {
                        if (item == ("CreateUser") || item == ("UpdateUser"))
                        {
                            fields.Add(item + "Name");
                            fields.Add(item + "Id");
                        }
                        else
                        {

                            var columnFind = correlationColumnJson.FirstOrDefault(f => f.Key == item);
                            if (columnFind != null)
                            {
                                fields.Add(columnFind.Model + " as " + item);

                                //处理关联数据
                                if (columnFind.Type == EnumZeroCodeControl.correlation.ToString() ||
                                    columnFind.Type == EnumZeroCodeControl.dictionary.ToString())
                                {
                                    format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                                    {
                                        Type = columnFind.Type,
                                        Key = item,
                                        Field = columnFind
                                    });
                                }
                            }
                            else
                            {
                                fields.Add(item);
                            }
                        }
                    }

                    if (!fields.Contains("RowId"))
                    {
                        fields.Add("RowId");
                    }

                    string sql = input.Sql +
                                  (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                                  (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");
                    StringBuilder orderBy = new StringBuilder();
                    if (input.Sort.IsNotNullOrEmpty())
                    {
                        for (int i = 0; i < input.Sort.Split(',').Length; i++)
                        {
                            orderBy.Append($"{correlationZeroCode.TableName + "." + input.Sort.Split(',')[i]} ,");
                        }
                    }
                    else
                    {
                        orderBy.Append(" Id asc ");
                    }

                    var querySql = "";
                    //判断当前类型是否是双向关联
                    if (correlationFind.Options.TwoWay.Is && correlationFind.Type != EnumZeroCodeControl.subtable.ToString())
                    {
                        var otherCollection = correlationColumnJson.FirstOrDefault(f => f.Options.TwoWay.Key == input.Key);
                        if (otherCollection != null)
                        {
                            querySql = $"select {fields.ExpandAndToString()},@rowNumber, @recordCount from {correlationZeroCode.TableName} @where and Status=1 and  JSON_CONTAINS({otherCollection.Model},JSON_ARRAY(\"{input.RowId}\")) ";
                            //直接查询关联表
                            var currentPage = input.Current; //当前页号
                            var pageSize = input.Size; //每页记录数
                            var lower = ((currentPage - 1) * pageSize) + 1; //记录起点
                            var upper = currentPage * pageSize; //记录终点


                            var querySqlQueryMultiple = input.IsReport ?
                                              $@"select * from ({querySql}) seq "
                                              : $@"select * from ({querySql}) seq where seq.row_num between {lower} and {upper}";

                            var orderString = $" {orderBy.ToString().TrimEnd(',')}";
                            var recordCount = querySqlQueryMultiple.Split("@recordCount");
                            var selectSql = recordCount[0].Trim().TrimEnd(',');
                            querySqlQueryMultiple = selectSql + " " + recordCount[1];
                            querySqlQueryMultiple = querySqlQueryMultiple.Replace("@rowNumber", " row_number() over (order by @orderBy) as row_num ")
                                .Replace("@orderBy", orderString)
                                .Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));

                            var querySqlRecordCount = querySql.Split("@recordCount");
                            var recordCountSql = querySql.Replace(querySqlRecordCount[0], ";select count(*) ").Replace("@recordCount", "  ").Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(sql) ? string.Empty : sql));
                            //总数量
                            querySqlQueryMultiple += recordCountSql;
                            var queryMulti = fix.Db.Connection.QueryMultiple(querySqlQueryMultiple);
                            data = queryMulti.Read<dynamic>().ToList();
                            v.count = queryMulti.Read<long>().Sum();
                        }

                    }
                    else
                    {
                        //根据当前的Id查询所有表
                        querySql = $"select {fields.ExpandAndToString()} from {correlationZeroCode.TableName} where Status=1 and RowId COLLATE utf8mb4_unicode_ci in (SELECT info.correlationRowId FROM {zeroCode.TableName},JSON_TABLE({correlationFind.Model},'$[*]' COLUMNS (correlationRowId VARCHAR (36) PATH '$' )) AS info where RowId='{input.RowId}') ";
                        var queryMulti = fix.Db.Connection.QueryMultiple(querySql);
                        data = queryMulti.Read<dynamic>().ToList();
                    }
                }

                //数据再次处理
                if (data.Any())
                {
                    if (columns.Contains("CreateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "CreateUser"
                        });
                    }
                    if (columns.Contains("UpdateUser"))
                    {
                        format.Add(new SystemWorkSheetFindPagingGridFieldDataInput
                        {
                            Type = "User",
                            Key = "UpdateUser"
                        });
                    }

                    //值对应的用户Id集合
                    List<string> userIds = new List<string>();
                    List<SystemUserHeaderOutput> user = new List<SystemUserHeaderOutput>();
                    if (format.Any(a => a.Type == "User"))
                    {
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                if (formatUsers)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().Split(',');
                                        foreach (var id in ids)
                                        {
                                            if (!userIds.Any(a => a == id))
                                            {
                                                userIds.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        if (userIds.Any())
                        {
                            //查询用户头像
                            var sql = $"select HeadImage,UserId from system_userinfo where UserId in ({userIds.ExpandAndToString().InSql()})";
                            using (var fix = new SqlDatabaseFixture())
                            {
                                user = (await fix.Db.Connection.QueryAsync<SystemUserHeaderOutput>(sql)).ToList();
                            }
                        }
                    }

                    //是否具有需要替换的Id,或者需要掩码
                    if (userIds.Any())
                    {
                        List<dynamic> tmp = new List<dynamic>();
                        foreach (var item in data)
                        {
                            dynamic info = new ExpandoObject();
                            var dic = (IDictionary<string, object>)info;
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                object dvalue = dd.Value;

                                if (dvalue != null)
                                {
                                    //头像处理
                                    var formatUsers = format.Any(a => a.Type == "User" && a.Key == dkey);
                                    if (formatUsers)
                                    {
                                        var userIdSplit = dvalue.ToString().Split(',');
                                        List<string> userIdHeader = new List<string>();
                                        foreach (var userId in userIdSplit)
                                        {
                                            userIdHeader.Add(user.FirstOrDefault(f => f.UserId.ToString() == userId)?.HeadImage);
                                        }
                                        dic.Add(dkey + "Header", userIdHeader.ExpandAndToString());
                                    }
                                }
                                dic.Add(dkey, dvalue);
                            }
                            tmp.Add(info);
                        }
                        data = tmp;
                    }

                    #region 关联数据
                    var correlations = format.Where(w => w.Type == EnumZeroCodeControl.correlation.ToString());
                    foreach (var correlation in correlations)
                    {
                        List<string> rowId = new List<string>();
                        foreach (var item in data)
                        {
                            foreach (var dd in item)
                            {
                                string dkey = dd.Key;
                                if (correlation.Key == dkey)
                                {
                                    object dvalue = dd.Value;
                                    if (dvalue != null)
                                    {
                                        var ids = dvalue.ToString().JsonStringToList<string>();
                                        foreach (var id in ids)
                                        {
                                            if (!rowId.Any(a => a == id))
                                            {
                                                rowId.Add(id);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        if (rowId.Any())
                        {
                            //得到配置信息
                            var correlationFind = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = (Guid)correlation.Field.Options.ZeroCodeId }, true)).Data;
                            //得到标题
                            var config = correlationFind.PublicJson.JsonStringToObject<SystemZeroCodePublicJsonWorkSheetOutput>();
                            //转换列
                            var columnJson = correlationFind.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                            var title = columnJson.FirstOrDefault(f => f.Key == config.Config.Title);
                            if (title != null)
                            {
                                var sql = $"select RowId,{title.Model} as Title from {correlationFind.TableName} where RowId in ({rowId.ExpandAndToString().InSql()})";
                                List<SystemZeroCodeCorrelationOutput> correlationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                using (var fix = new SqlDatabaseFixture())
                                {
                                    correlationOutputs = (await fix.Db.Connection.QueryAsync<SystemZeroCodeCorrelationOutput>(sql)).ToList();
                                }
                                List<dynamic> tmp = new List<dynamic>();
                                foreach (var item in data)
                                {
                                    dynamic info = new ExpandoObject();
                                    var dic = (IDictionary<string, object>)info;
                                    foreach (var dd in item)
                                    {
                                        string dkey = dd.Key;
                                        object dvalue = dd.Value;

                                        if (correlation.Key == dkey && dvalue != null)
                                        {
                                            //数据处理
                                            var ids = dvalue.ToString().JsonStringToList<Guid>();
                                            List<SystemZeroCodeCorrelationOutput> codeCorrelationOutputs = new List<SystemZeroCodeCorrelationOutput>();
                                            foreach (var id in ids)
                                            {
                                                var find = correlationOutputs.FirstOrDefault(f => f.RowId == id);
                                                codeCorrelationOutputs.Add(find);
                                            }
                                            dic.Add(dkey, JsonConvert.SerializeObject(codeCorrelationOutputs));
                                        }
                                        else
                                        {
                                            dic.Add(dkey, dvalue);
                                        }
                                    }
                                    tmp.Add(info);
                                }
                                data = tmp;

                            }
                        }
                    }

                    #endregion
                }

                v.data = data;
                return OperateStatus<dynamic>.Success(v);
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 查询业务数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<List<SystemWorkSheetFilterSearchOutput>>> FindFilterSearch(SystemWorkSheetFilterSearchInput input)
        {
            var operateStatus = new OperateStatus<List<SystemWorkSheetFilterSearchOutput>>();
            try
            {
                //input.Field = input.Format.IsNullOrEmpty() ? input.Field : input.Field.Replace("_Txt", "");
                //var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId }, true)).Data;
                //if (zeroCode.TableName.IsNullOrEmpty())
                //{
                //    operateStatus.Msg = "请配置数据源";
                //    return operateStatus;
                //}
                //var masks = new List<SystemDataBaseFindPagingBusinessDataTableColunmsInput>();
                //var format = new List<SystemDataBaseFindPagingBusinessDataTableColunmsInput>();
                //List<SystemDataBaseFindBusinessDataFilterSearchOutput> data = new List<SystemDataBaseFindBusinessDataFilterSearchOutput>();
                //SqlDatabaseFixtureInput sqlDatabaseFixtureInput = new SqlDatabaseFixtureInput();

                //sqlDatabaseFixtureInput.ConnectionType = dataBase.ConnectionType;
                //sqlDatabaseFixtureInput.ConnectionString = dataBase.ConnectionString;

                //if (input.HaveDataPermission)
                //{
                //    var currentUser = EipHttpContext.CurrentUser();
                //    input.Rote.UserId = currentUser.UserId;
                //    input.DataSql = (await _permissionLogic.FindDataPermissionSql(input.Rote)).Data;
                //}
                //using (var fix = new SqlDatabaseFixture(sqlDatabaseFixtureInput))
                //{
                //    var tables = zeroCode.PublicJson.JsonStringToObject<SystemDataBaseFindPagingBusinessDataTableInput>();
                //    string selectSql = "";

                //    if (input.Format.IsNullOrEmpty())
                //    {
                //        selectSql = $"SELECT {input.Field} Field,{input.Field} Field_Txt,count({input.Field}) AS count FROM {zeroCode.TableName} where  {input.Field} is not null {input.DataSql} group by {input.Field}";
                //        selectSql += " union ";
                //        selectSql += $" select '-1' type,'为空' type_txt,count(*) AS Count from {zeroCode.TableName} where {input.Field} is  null ";
                //    }
                //    else
                //    {
                //        selectSql = $"SELECT {input.Field} Field,{input.Field + "_Txt"} Field_Txt,count({input.Field}) AS count FROM {zeroCode.TableName} where 1=1 and isDelete=0 and {input.Field} is not null {input.DataSql} group by {input.Field},{input.Field + "_Txt"}";
                //        selectSql += " union ";
                //        selectSql += $" select '-1' type,'为空' type_txt,count(*) AS Count from {zeroCode.TableName} where {input.Field} is  null  and isDelete=0 ";
                //    }

                //    data = (await fix.Db.Connection.QueryAsync<SystemDataBaseFindBusinessDataFilterSearchOutput>(selectSql)).ToList();
                //    operateStatus.Data = data;
                //    operateStatus.Code = ResultCode.Success;
                //    operateStatus.Msg = Chs.QuerySuccessful;
                //}
                return operateStatus;
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 查询业务数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindFooter(SystemWorkSheetFindFooterInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                var user = EipHttpContext.CurrentUser();
                using (var fix = new SqlDatabaseFixture())
                {
                    var gridCustom = await fix.Db.SystemZeroCodeGridCustom.SetSelect(s => new { s.Config }).FindAsync(f => f.Key == input.Key && f.CreateUserId == user.UserId);
                    //得到需要查询的字段
                    var tables = gridCustom.Config.JsonStringToObject<SystemWorkSheetFindFooterConfigOutput>();

                    var zeroCode = (await _zeroCodeLogic.FindColumnJsonJsonById(new IdInput { Id = input.ZeroCodeId })).Data;
                    var columns = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();

                    //判断是否分页
                    StringBuilder fields = new StringBuilder();
                    //得到需要查询的字段
                    StringBuilder numberSql = new StringBuilder();
                    foreach (var item in tables.SheetSummary.Where(w => w.Type != "no"))
                    {
                        var findColumn = columns.FirstOrDefault(f => f.Key == item.Key);
                        if (findColumn != null)
                        {
                            switch (item.Type)
                            {
                                case "sum":
                                    fields.Append($"sum(CONVERT({zeroCode.TableName + "." + findColumn.Model},DECIMAL)){item.Key},");
                                    break;
                                case "avg":
                                    fields.Append($"avg(CONVERT({zeroCode.TableName + "." + findColumn.Model},DECIMAL)){item.Key},");
                                    break;
                                case "max":
                                    fields.Append($"max(CONVERT({zeroCode.TableName + "." + findColumn.Model},DECIMAL)){item.Key},");
                                    break;
                                case "min":
                                    fields.Append($"min(CONVERT({zeroCode.TableName + "." + findColumn.Model},DECIMAL)){item.Key},");
                                    break;
                                case "nn":
                                    if (findColumn.Type == EnumZeroCodeControl.correlation.ToString())
                                    {
                                        numberSql.Append($"SELECT count(1) {item.Key} FROM {zeroCode.TableName} where {findColumn.Model} is null or JSON_LENGTH({findColumn.Model})=0; ");
                                    }
                                    else
                                    {
                                        numberSql.Append($"SELECT count(1) {item.Key} FROM {zeroCode.TableName} where {findColumn.Model} is null; ");
                                    }

                                    break;
                                case "nu":
                                    if (findColumn.Type == EnumZeroCodeControl.correlation.ToString())
                                    {
                                        numberSql.Append($"SELECT count(1) {item.Key} FROM {zeroCode.TableName} where {findColumn.Model} is not null AND JSON_LENGTH({findColumn.Model})>0; ");
                                    }
                                    else
                                    {
                                        numberSql.Append($"SELECT count(1) {item.Key} FROM {zeroCode.TableName} where {findColumn.Model} is not null; ");
                                    }

                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                    dynamic data = null;
                    if (fields.Length > 0)
                    {
                        input.Fields = fields.ToString().TrimEnd(',');
                        string sql = input.Sql +
                                      (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                                      (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");
                        string selectSql = $"SELECT {input.Fields} FROM {zeroCode.TableName} where 1=1 {sql} ";
                        data = (await fix.Db.Connection.QueryAsync<dynamic>(selectSql)).ToList();
                    }
                    if (numberSql.ToString().IsNotNullOrEmpty())
                    {
                        List<dynamic> tmp = new List<dynamic>();
                        dynamic info = new ExpandoObject();
                        var dic = (IDictionary<string, object>)info;
                        if (data != null)
                        {
                            foreach (var ditem in data)
                            {
                                foreach (var dd in ditem)
                                {
                                    string dkey = dd.Key;
                                    object dvalue = dd.Value;
                                    dic.Add(dkey, dvalue);
                                }
                            }
                        }

                        var queryMulti = fix.Db.Connection.QueryMultiple(numberSql.ToString());
                        foreach (var item in tables.SheetSummary.Where(w => w.Type != "no"))
                        {
                            var findColumn = columns.FirstOrDefault(f => f.Key == item.Key);
                            if (findColumn != null)
                            {
                                switch (item.Type)
                                {
                                    case "nu":
                                    case "nn":
                                        var count = queryMulti.Read<long>().Sum();
                                        dic.Add(item.Key, count);
                                        break;
                                    default:
                                        break;
                                }
                            }
                        }
                        tmp.Add(info);
                        data = tmp;
                    }
                    operateStatus.Data = data;
                    operateStatus.Msg = Chs.QuerySuccessful;
                    operateStatus.Code = ResultCode.Success;
                }
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> Delete(SystemWorkSheetDeleteInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //获取模板数据
            var currentUser = EipHttpContext.CurrentUser();
            var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId })).Data;

            string connectionType = dataBase.ConnectionType;
            string connectionString = dataBase.ConnectionString;
            using (var fix = new SqlDatabaseFixture(new SqlDatabaseFixtureInput
            {
                ConnectionString = connectionString,
                ConnectionType = connectionType,
            }))
            {
                StringBuilder stringBuilder = new StringBuilder();
                //逻辑删除
                stringBuilder.Append($" UPDATE {zeroCode.TableName} SET " +
                    $"Status=9," +
                    $"UpdateTime='{DateTime.Now}'," +
                    $"UpdateUserName='{currentUser.Name}'," +
                    $"UpdateUserId='{currentUser.UserId}'," +
                    $"UpdateOrganizationId='{currentUser.OrganizationId}'," +
                    $"UpdateOrganizationName='{currentUser.OrganizationName}'" +
                    $" WHERE RowId in ({input.Id.InSql()}) ");
                if (await fix.Db.Connection.ExecuteAsync(stringBuilder.ToString()) > 0)
                {
                    operateStatus.Code = ResultCode.Success;
                    operateStatus.Msg = Chs.Successful;
                }
            }
            return operateStatus;
        }

        /// <summary>
        /// 删除选中
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> DeletePhysics(SystemWorkSheetDeleteInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //获取模板数据
            var currentUser = EipHttpContext.CurrentUser();
            var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId })).Data;

            string connectionType = dataBase.ConnectionType;
            string connectionString = dataBase.ConnectionString;
            using (var fix = new SqlDatabaseFixture(new SqlDatabaseFixtureInput
            {
                ConnectionString = connectionString,
                ConnectionType = connectionType,
            }))
            {
                StringBuilder stringBuilder = new StringBuilder();
                //逻辑删除
                stringBuilder.Append($" UPDATE {zeroCode.TableName} SET " +
                    $"Status=999," +
                    $"UpdateTime='{DateTime.Now}'," +
                    $"UpdateUserName='{currentUser.Name}'," +
                    $"UpdateUserId='{currentUser.UserId}'," +
                    $"UpdateOrganizationId='{currentUser.OrganizationId}'," +
                    $"UpdateOrganizationName='{currentUser.OrganizationName}'" +
                    $" WHERE RowId in ({input.Id.InSql()}) ");
                if (await fix.Db.Connection.ExecuteAsync(stringBuilder.ToString()) > 0)
                {
                    operateStatus.Code = ResultCode.Success;
                    operateStatus.Msg = Chs.Successful;
                }
            }
            return operateStatus;
        }

        /// <summary>
        /// 彻底删除全部
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> DeletePhysicsAll(IdInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //获取模板数据
            var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.Id })).Data;
            var currentUser = EipHttpContext.CurrentUser();
            string connectionType = dataBase.ConnectionType;
            string connectionString = dataBase.ConnectionString;
            using (var fix = new SqlDatabaseFixture(new SqlDatabaseFixtureInput
            {
                ConnectionString = connectionString,
                ConnectionType = connectionType,
            }))
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($" UPDATE {zeroCode.TableName} SET " +
                    $"Status=999," +
                    $"UpdateTime='{DateTime.Now}'," +
                    $"UpdateUserName='{currentUser.Name}'," +
                    $"UpdateUserId='{currentUser.UserId}'," +
                    $"UpdateOrganizationId='{currentUser.OrganizationId}'," +
                    $"UpdateOrganizationName='{currentUser.OrganizationName}'" +
                    $" WHERE Status=9  ");
                if (await fix.Db.Connection.ExecuteAsync(stringBuilder.ToString()) > 0)
                {
                    operateStatus.Code = ResultCode.Success;
                    operateStatus.Msg = Chs.Successful;
                }
            }
            return operateStatus;
        }

        /// <summary>
        /// 彻底删除选择
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> RecoveryDeletePhysics(SystemWorkSheetDeleteInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //获取模板数据
            var currentUser = EipHttpContext.CurrentUser();
            var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = input.ZeroCodeId })).Data;
            string connectionType = dataBase.ConnectionType;
            string connectionString = dataBase.ConnectionString;
            using (var fix = new SqlDatabaseFixture(new SqlDatabaseFixtureInput
            {
                ConnectionString = connectionString,
                ConnectionType = connectionType,
            }))
            {
                StringBuilder stringBuilder = new StringBuilder();
                //逻辑删除
                stringBuilder.Append($" UPDATE {zeroCode.TableName} SET " +
                    $"Status=1," +
                    $"UpdateTime='{DateTime.Now}'," +
                    $"UpdateUserName='{currentUser.Name}'," +
                    $"UpdateUserId='{currentUser.UserId}'," +
                    $"UpdateOrganizationId='{currentUser.OrganizationId}'," +
                    $"UpdateOrganizationName='{currentUser.OrganizationName}'" +
                    $" WHERE RowId in ({input.Id.InSql()}) ");
                if (await fix.Db.Connection.ExecuteAsync(stringBuilder.ToString()) > 0)
                {
                    operateStatus.Code = ResultCode.Success;
                    operateStatus.Msg = Chs.Successful;
                }
            }
            return operateStatus;
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> Import(SystemWorkSheetImportInput input)
        {
            OperateStatus<Guid> operateStatus = new OperateStatus<Guid>();
            try
            {
                IList<string> doSqls = new List<string>();
                List<Guid> ids = new List<Guid>();
                //参数
                var parameters = new Dictionary<string, object>();
                using (var fix = new SqlDatabaseFixture())
                {
                    var zeroCode = await fix.Db.SystemZeroCode.SetSelect(s => new { s.PublicJson, s.TableName, s.ColumnJson }).FindAsync(f => f.ZeroCodeId == input.ZeroCodeId);
                    var clos = new List<SystemDataBaseImportColsInput>();
                    clos = input.Clos.Where(a => a.Field != null).ToList();

                    var columns = zeroCode.ColumnJson.JsonStringToList<SystemDataBaseColumnJsonInput>();

                    string wField = "", wValue = "";
                    //导入是否做删除操作
                    if (input.Type == 0)
                    {
                        string where = string.IsNullOrEmpty(input.WhereField) ? "" : " WHERE " + input.WhereValue;
                        doSqls.Add($"DELETE FROM {zeroCode.TableName}{where}");
                        foreach (var item in clos)
                        {
                            item.Title = item.Title.Trim();
                            var column = columns.FirstOrDefault(f => f.Model == item.Field || f.Model + "_Txt" == item.Field);
                            if (column != null)
                            {
                                switch (column.Type)
                                {
                                    case "serialNo":
                                        fix.Db.Connection.Execute($"DELETE FROM system_serialno WHERE ZeroCodeId='{input.ZeroCodeId}' and Model='{item.Field}'");
                                        break;
                                    default:

                                        break;
                                }
                            }
                        }
                    }
                    //设置条件
                    if (!string.IsNullOrEmpty(input.WhereField) && !input.WhereField.Equals("null"))
                    {
                        var arr = input.WhereField.Split("=");
                        wField = arr[0];
                        wValue = arr[1];
                    }
                    var tableClos = new List<string>();
                    if (input.Data.Any())
                    {
                        var rows = input.Data.FirstOrDefault();
                        foreach (var row in rows)
                        {
                            var key = row.Key;
                            var value = row.Value;
                            tableClos.Add(key);
                        }

                        string symbol = RepositoryUtil.GetSymbol();

                        var datas = input.Data.ToList();
                        for (int i = 0; i < datas.Count(); i++)
                        {
                            try
                            {
                                var data = datas[i];
                                StringBuilder stringBuilder = new StringBuilder();
                                StringBuilder stringBuilderValues = new StringBuilder();
                                stringBuilder.Append($"INSERT INTO {zeroCode.TableName} (");
                                string sql;
                                Guid id = CombUtil.NewComb();
                                ids.Add(id);
                                IList<SystemDataBaseSaveBusinessData> columnsSaveBusinessData = new List<SystemDataBaseSaveBusinessData>();
                                foreach (var item in clos)
                                {
                                    item.Title = item.Title.Trim();
                                    if (tableClos.Any(t => t == item.Title))
                                    {
                                        string value = data[item.Title].ToString().Trim();
                                        columnsSaveBusinessData.Add(new SystemDataBaseSaveBusinessData
                                        {
                                            Key = item.Field,
                                            Value = value
                                        });
                                    }
                                }
                                foreach (var item in clos)
                                {
                                    item.Title = item.Title.Trim();
                                    if (tableClos.Any(t => t == item.Title))
                                    {
                                        string value = data[item.Title].ToString().Trim();
                                        var column = columns.FirstOrDefault(f => f.Model == item.Field);
                                        if (column != null && value.IsNotNullOrEmpty())
                                        {
                                            switch (column.Type)
                                            {
                                                case "select":
                                                    {
                                                        SystemDataBaseColumnOptionSelect optionSelect = JsonConvert.DeserializeObject<SystemDataBaseColumnOptionSelect>(column.Options.ToString());
                                                        if (optionSelect.dynamic)
                                                        {

                                                        }
                                                        else
                                                        {
                                                            var optionValue = optionSelect.options.FirstOrDefault(f => f.label.Trim() == value.Trim());
                                                            if (optionValue != null)
                                                            {
                                                                var field = item.Field.Replace("_Txt", "");
                                                                stringBuilder.Append($"{field},");
                                                                stringBuilderValues.Append($"{symbol}{field}{i},");
                                                                parameters.Add($"{field}{i}", optionValue.value);
                                                            }
                                                        }
                                                    }
                                                    break;
                                                case "correlationRecord":
                                                    //{
                                                    //    SystemDataBaseColumnOptionCorrelationRecord optionCorrelationRecord = JsonConvert.DeserializeObject<SystemDataBaseColumnOptionCorrelationRecord>(column.Options.ToString());
                                                    //    if (optionCorrelationRecord.dynamicConfig.table.HasValue)
                                                    //    {
                                                    //        //获取工作表
                                                    //        var zeroCode = await fix.Db.SystemZeroCode.SetSelect(s => new { s.TableName }).FindAsync(f => f.ZeroCodeId == (Guid)optionCorrelationRecord.dynamicConfig.table);
                                                    //        if (zeroCode != null)
                                                    //        {

                                                    //            string connectionType = dataBase.ConnectionType;
                                                    //            string connectionString = dataBase.ConnectionString;
                                                    //            using (var db = GetConnectoin(dataBase.ConnectionString, dataBase.ConnectionType))
                                                    //            {
                                                    //                var field = optionCorrelationRecord.columns.Where(w => w.isShow).FirstOrDefault().name;
                                                    //                var sqlCorrelationRecord = "select RowId from " + zeroCode.TableName + $" where {field}='{value.Trim()}'";
                                                    //                try
                                                    //                {
                                                    //                    var correlationRecordDatas = await db.QueryAsync<SystemDataBaseColumnOptionCorrelationRecordDataOutput>(sqlCorrelationRecord);
                                                    //                    if (correlationRecordDatas != null && correlationRecordDatas.Any())
                                                    //                    {
                                                    //                        var fieldR = item.Field.Replace("_Txt", "");
                                                    //                        stringBuilder.Append($"{fieldR},");
                                                    //                        stringBuilderValues.Append($"{symbol}{fieldR}{i},");
                                                    //                        parameters.Add($"{fieldR}{i}", correlationRecordDatas.FirstOrDefault().RowId);
                                                    //                    }
                                                    //                }
                                                    //                catch (Exception ex)
                                                    //                {
                                                    //                    throw;
                                                    //                }
                                                    //            }
                                                    //        }
                                                    //    }
                                                    //}
                                                    break;
                                                case "radio":
                                                    {
                                                        SystemDataBaseColumnOptionSelect optionSelect = JsonConvert.DeserializeObject<SystemDataBaseColumnOptionSelect>(column.Options.ToString());
                                                        if (optionSelect.dynamic)
                                                        {

                                                        }
                                                        else
                                                        {
                                                            var optionValue = optionSelect.options.FirstOrDefault(f => f.label.Trim() == value.Trim());
                                                            if (optionValue != null)
                                                            {
                                                                var field = item.Field.Replace("_Txt", "");
                                                                stringBuilder.Append($"{field},");
                                                                stringBuilderValues.Append($"{symbol}{field}{i},");
                                                                parameters.Add($"{field}{i}", optionValue.value);
                                                            }
                                                        }
                                                    }
                                                    break;
                                                case "user":
                                                    {
                                                        var org = await fix.Db.SystemUserInfo.SetSelect(s => new { s.UserId }).FindAsync(f => f.Name == value);
                                                        if (org != null)
                                                        {
                                                            var field = item.Field.Replace("_Txt", "");
                                                            stringBuilder.Append($"{field},");
                                                            stringBuilderValues.Append($"{symbol}{field}{i},");
                                                            parameters.Add($"{field}{i}", org.UserId);
                                                        }
                                                    }
                                                    break;
                                                case "organization":
                                                    {
                                                        var org = await fix.Db.SystemOrganization.SetSelect(s => new { s.OrganizationId }).FindAsync(f => f.Name == value);
                                                        if (org != null)
                                                        {
                                                            var field = item.Field.Replace("_Txt", "");
                                                            stringBuilder.Append($"{field},");
                                                            stringBuilderValues.Append($"{symbol}{field}{i},");
                                                            parameters.Add($"{field}{i}", org.OrganizationId);
                                                        }
                                                    }
                                                    break;
                                                default:
                                                    break;
                                            }
                                            stringBuilder.Append($"{item.Field},");
                                            stringBuilderValues.Append($"{symbol}{item.Field}{i},");
                                            parameters.Add($"{item.Field}{i}", value.FilterSql());
                                        }
                                        else
                                        {
                                            if (column != null)
                                            {
                                                switch (column.Type)
                                                {
                                                    case "serialNo"://自增编号
                                                        {
                                                            SystemDataBaseColumnsOptionInput row = JsonConvert.DeserializeObject<SystemDataBaseColumnsOptionInput>(column.Options.ToString());

                                                            var noCreate = await _systemSerialNoLogic.Create(new SystemSerialCreateInput
                                                            {
                                                                //Columns = columnsSaveBusinessData,
                                                                Rule = row.Rule.ObjectToJsonString(),
                                                                ZeroCodeId = input.ZeroCodeId,
                                                                LoadDisplay = row.LoadDisplay,
                                                                Key = item.Field,
                                                                UserOccupation = row.UserOccupation
                                                            });
                                                            //更新值
                                                            if (noCreate.Code == ResultCode.Success)
                                                            {
                                                                var field = item.Field.Replace("_Txt", "");
                                                                stringBuilder.Append($"{field},");
                                                                stringBuilderValues.Append($"{symbol}{field}{i},");
                                                                parameters.Add($"{field}{i}", noCreate.Data);
                                                            }

                                                            if (row.UserOccupation)
                                                            {
                                                                //更新用户编码
                                                                _systemSerialNoLogic.Clear(input.ZeroCodeId, item.Field);
                                                            }
                                                        }
                                                        break;
                                                    default:

                                                        break;
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        //序号处理
                                        var column = columns.FirstOrDefault(f => f.Model == item.Field);
                                        if (column != null)
                                        {
                                            switch (column.Type)
                                            {
                                                case "serialNo"://自增编号
                                                    {
                                                        SystemDataBaseColumnsOptionInput row = JsonConvert.DeserializeObject<SystemDataBaseColumnsOptionInput>(column.Options.ToString());

                                                        var noCreate = await _systemSerialNoLogic.Create(new SystemSerialCreateInput
                                                        {
                                                            //Columns = columnsSaveBusinessData,
                                                            Rule = row.Rule.ObjectToJsonString(),
                                                            ZeroCodeId = input.ZeroCodeId,
                                                            LoadDisplay = row.LoadDisplay,
                                                            Key = item.Field,
                                                            UserOccupation = row.UserOccupation
                                                        });
                                                        //更新值
                                                        if (noCreate.Code == ResultCode.Success)
                                                        {
                                                            var field = item.Field.Replace("_Txt", "");
                                                            stringBuilder.Append($"{field},");
                                                            stringBuilderValues.Append($"{symbol}{field}{i},");
                                                            parameters.Add($"{field}{i}", noCreate.Data);
                                                        }

                                                        if (row.UserOccupation)
                                                        {
                                                            //更新用户编码
                                                            _systemSerialNoLogic.Clear(input.ZeroCodeId, item.Field);
                                                        }
                                                    }
                                                    break;
                                                default:

                                                    break;
                                            }
                                        }
                                    }
                                }
                                //主键
                                stringBuilder.Append("RowId,");
                                stringBuilderValues.Append($"'{id}',");

                                stringBuilder.Append("CreateUserId,");
                                stringBuilderValues.Append($"'{input.UserId}',");

                                stringBuilder.Append("CreateUserName,");
                                stringBuilderValues.Append($"'{input.UserName}',");

                                stringBuilder.Append("CreateOrganizationId,");
                                stringBuilderValues.Append($"'{input.OrganizationId}',");

                                stringBuilder.Append("CreateOrganizationName,");
                                stringBuilderValues.Append($"'{input.OrganizationName}',");

                                stringBuilder.Append("Status,");
                                stringBuilderValues.Append($"1,");

                                //参数带过来的
                                if (!string.IsNullOrEmpty(input.WhereField) && !input.WhereField.Equals("null"))
                                {
                                    stringBuilder.Append(wField + ",");
                                    stringBuilderValues.Append($"'{wValue}',");
                                }

                                //拼接Sql
                                sql = stringBuilder.ToString().TrimEnd(',') + " ) VALUES (" +
                                      stringBuilderValues.ToString().TrimEnd(',') + ")";
                                if (stringBuilder.ToString().IsNotNullOrEmpty())
                                {
                                    doSqls.Add(sql);
                                }
                            }
                            catch (Exception e)
                            {
                                operateStatus.Msg = "操作失败:" + e.Message;
                                operateStatus.Code = ResultCode.Error;
                            }
                        }
                    }
                }
                if (doSqls.Any())
                {
                    string connectionType = dataBase.ConnectionType;
                    string connectionString = dataBase.ConnectionString;
                    using (var fix = new SqlDatabaseFixture(new SqlDatabaseFixtureInput
                    {
                        ConnectionString = connectionString,
                        ConnectionType = connectionType,
                    }))
                    {
                        var trans = fix.Db.Connection.BeginTransaction();
                        try
                        {
                            var totalInstances = doSqls.Count();
                            int maxAllowedInstancesPerBatch = 50;
                            //总页数
                            int exceededTimes = (int)Math.Ceiling(Convert.ToDouble(totalInstances) / maxAllowedInstancesPerBatch);
                            if (exceededTimes > 1)
                            {
                                for (int i = 0; i <= exceededTimes; i++)
                                {
                                    var skips = i * maxAllowedInstancesPerBatch;

                                    if (skips >= totalInstances)
                                        break;

                                    var items = doSqls.Skip(skips).Take(maxAllowedInstancesPerBatch);
                                    fix.Db.Connection.Execute(items.ExpandAndToString(";"), parameters, trans);
                                }
                            }
                            else
                            {
                                fix.Db.Connection.Execute(doSqls.ExpandAndToString(";"), parameters, trans);
                            }
                            trans.Commit();

                            //写入日志
                            foreach (var item in ids)
                            {
                                SystemZeroCodeDataLog systemZeroCodeDataLog = new SystemZeroCodeDataLog();
                                systemZeroCodeDataLog = new SystemZeroCodeDataLog()
                                {
                                    Type = EnumZeroCodeDataLogType.新增.ToShort(),
                                    ZeroCodeDataLogId = CombUtil.NewComb(),
                                    CreateUserId = input.UserId,
                                    CreateUserName = input.UserName,
                                    CreateTime = DateTime.Now,
                                    RowId = item,
                                    ZeroCodeId = input.ZeroCodeId
                                };
                                await _publisher.PublishAsync("ZeroCodeDataLogSaveSubscribe", systemZeroCodeDataLog);
                            }

                            operateStatus.Code = ResultCode.Success;
                            operateStatus.Msg = Chs.Successful;
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            operateStatus.Msg = ex.Message;
                            operateStatus.Code = ResultCode.Error;
                            return operateStatus;
                        }
                    }
                }
                else
                {
                    operateStatus.Code = ResultCode.Success;
                }

            }
            catch (Exception e)
            {
                operateStatus.Msg = "操作失败:" + e.Message;
                operateStatus.Code = ResultCode.Error;
            }
            return operateStatus;
        }

        /// <summary>
        /// 刷新关联字段
        /// </summary>
        /// <param name="triggerZeroCodeId">触发工作表</param>
        /// <param name="triggerZeroCodeKey">触发字段</param>
        /// <param name="rowId">当前记录Id</param>
        /// <param name="afterData">替换值</param>
        /// <returns></returns>
        public async Task SetReloadLink(Guid triggerZeroCodeId, string triggerZeroCodeKey, Guid rowId, object afterData, List<string> updateSqls)
        {
            //查询是否具有改变字段
            using (var fix = new SqlDatabaseFixture())
            {
                var find = fix.Db.SystemZeroCodeReloadLink.FindAll(f => f.TriggerZeroCodeId == triggerZeroCodeId && f.TriggerZeroCodeKey == triggerZeroCodeKey);
                foreach (var item in find)
                {
                    //刷新的是关联字段
                    if (item.ReloadZeroCodeType == EnumZeroCodeControl.otherField.ToString())
                    {
                        var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = item.ReloadZeroCodeId }, true)).Data;
                        if (zeroCode.TableName.IsNotNullOrEmpty())
                        {
                            var columns = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                            //更新字段
                            var columnFind = columns.FirstOrDefault(f => f.Key == item.ReloadZeroCodeKey);
                            if (columnFind != null)
                            {
                                var otherFiled = columns.FirstOrDefault(f => f.Key == columnFind.Options.Key);
                                string updateSql = $"UPDATE {zeroCode.TableName} SET {columnFind.Model}='{afterData}' WHERE JSON_CONTAINS({otherFiled.Model}, '\"{rowId}\"', '$')>0";
                                updateSqls.Add(updateSql);
                                //得到所有受影响的值
                                var findDataSql = $"SELECT RowId FROM  {zeroCode.TableName} WHERE JSON_CONTAINS({otherFiled.Model}, '\"{rowId}\"', '$')>0";
                                var findDatas = fix.Db.Connection.Query<Guid>(findDataSql);
                                foreach (var findDatasRowId in findDatas)
                                {
                                    await SetReloadLink(item.ReloadZeroCodeId, item.ReloadZeroCodeKey, findDatasRowId, afterData, updateSqls);
                                }
                            }
                        }
                    }
                    if (item.ReloadZeroCodeType == EnumZeroCodeControl.formulaNumber.ToString())
                    {
                        var zeroCode = (await _zeroCodeLogic.FindPublicJsonById(new IdInput { Id = item.ReloadZeroCodeId }, true)).Data;
                        if (zeroCode.TableName.IsNotNullOrEmpty())
                        {
                            var columns = zeroCode.ColumnJson.JsonStringToList<SystemWorkSheetTableFieldDetailInput>();
                            //更新字段
                            var columnFind = columns.FirstOrDefault(f => f.Key == item.ReloadZeroCodeKey);
                            if (columnFind != null)
                            {
                                var updateAfterData = await FindById(new SystemWorkSheetFindByIdInput
                                {
                                    Id = rowId,
                                    ZeroCodeId = triggerZeroCodeId
                                });
                                string param = "";
                                //解析Code
                                foreach (var dd in updateAfterData.Data)
                                {
                                    string dkey = dd.Key;
                                    object dvalue = dd.Value;
                                    //判断类型
                                    var rowFind = columns.FirstOrDefault(a => a.Model == dkey);
                                    if (rowFind != null)
                                    {
                                        if (dkey == item.TriggerZeroCodeKey)
                                        {
                                            //将要刷新值替换
                                            dvalue = afterData;
                                        }
                                        if (dvalue == null)
                                        {
                                            dvalue = 0;
                                        }
                                        if (rowFind.Type == EnumZeroCodeControl.total.ToString() ||
                                            rowFind.Type == EnumZeroCodeControl.enumber.ToString())
                                        {
                                            param += "var " + dkey + "=" + dvalue + ";";
                                        }
                                        else
                                        {
                                            param += "var " + dkey + "='" + dvalue + "';";
                                        }
                                    }
                                }
                                var engine = new ScriptEngine();
                                //解析表达式
                                var code = ReplaceEditor(columnFind.Options.Code);
                                var h1Elements = code.GetNodes();
                                if (h1Elements != null)
                                {
                                    foreach (var element in h1Elements)
                                    {
                                        //解码
                                        var columnName = HttpUtility.HtmlDecode(element.Id);
                                        //转换key
                                        var convert = JsonConvert.DeserializeObject<SystemWorkSheetFindElementKeyOutput>(columnName);
                                        //得到转换值
                                        code = code.Replace(element.OuterHtml, convert.Key);
                                    }
                                    param += code;
                                    var result = engine.Evaluate(param);
                                    var updateSql = $"update {zeroCode.TableName} SET {columnFind.Model}={result} where Status=1 and RowId='{rowId}'";
                                    updateSqls.Add(updateSql);
                                    await SetReloadLink(item.ReloadZeroCodeId, item.ReloadZeroCodeKey, rowId, result, updateSqls);
                                }
                            }
                        }
                    }
                }
            }
        }
        #endregion

        #region 列表查询组装

        /// <summary>
        /// 处理SQL查询条件，含子查询
        /// </summary>
        /// <param name="filter">查询对象</param>
        /// <returns></returns>
        public static string ConvertFilters(Filter filter, List<SystemWorkSheetTableFieldDetailInput> field)
        {
            if (filter == null)
                return "";

            //标识是否为首次加载有效字段条件，作为添加AND或OR关键字
            bool isFirst = true;

            StringBuilder where = new StringBuilder("");

            //处理字段查询明细
            if (filter.rules != null && filter.rules.Count > 0)
            {
                foreach (var item in filter.rules)
                {
                    if (!string.IsNullOrEmpty(item.field) && !string.IsNullOrEmpty(item.op))
                    {
                        if (isFirst != true)
                        {
                            //非首个条件添加AND或者OR
                            where.AppendFormat(" {0} ", filter.groupOp);
                        }
                        where.Append(ConvertFilters(item, field));
                        isFirst = false;
                    }
                }
            }

            //处理嵌套查询
            if (filter.groups != null && filter.groups.Count > 0)
            {
                foreach (var item in filter.groups)
                {
                    string child = ConvertFilters(item, field);
                    if (!string.IsNullOrEmpty(child))
                    {
                        if (isFirst != true)
                        {
                            //非首个条件添加AND或者OR
                            where.AppendFormat(" {0} ", filter.groupOp);
                        }
                        where.Append(child);
                        isFirst = false;
                    }
                }
            }

            //返回
            return where.Length > 0 ? $" ({where}) " : "";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="field"></param>
        /// <param name="values"></param>
        /// <param name="worksheet"></param>
        /// <returns></returns>
        public static void CheckResultAllNull(Filter filter,
            List<SystemWorkSheetTableFieldDetailInput> field,
            List<SystemDataBaseSaveBusinessData> values,
            SystemDataBaseColumnsOptionDefaultValueConfigWorksheet worksheet,
            Dictionary<string, bool> checkResultAllNull)
        {
            if (filter != null)
            {
                //是否全部为空检测结果
                StringBuilder where = new StringBuilder("");
                //处理字段查询明细
                if (filter.rules != null && filter.rules.Count > 0)
                {
                    foreach (var item in filter.rules)
                    {
                        if (!string.IsNullOrEmpty(item.field) && !string.IsNullOrEmpty(item.op))
                        {
                            var fieldFind = field.FirstOrDefault(x => x.Key == item.field);
                            //处理值,若是动态值
                            if (item.opType == "2")
                            {
                                //转换值
                                var data = values.FirstOrDefault(f => f.Key == item.data);
                                //如果是关联字段
                                if (fieldFind.Type == EnumZeroCodeControl.correlation.ToString())
                                {
                                    if (data != null)
                                    {
                                        item.data = data.Value == null ? null : data.Value.ToString().JsonStringToList<string>().ExpandAndToString();
                                    }
                                }
                                //判断类型，是否忽略值为空情况
                                checkResultAllNull.Add(item.field, item.data.IsNullOrEmpty());
                            }
                        }
                    }
                }

                //处理嵌套查询
                if (filter.groups != null && filter.groups.Count > 0)
                {
                    foreach (var item in filter.groups)
                    {
                        CheckResultAllNull(item, field, values, worksheet, checkResultAllNull);
                    }
                }
            }
        }

        /// <summary>
        /// 处理SQL查询条件，含子查询
        /// </summary>
        /// <param name="filter">查询对象</param>
        /// <returns></returns>
        public static string ConvertFiltersWorkSheet(Filter filter,
            List<SystemWorkSheetTableFieldDetailInput> field,
            List<SystemDataBaseSaveBusinessData> values,
            SystemDataBaseColumnsOptionDefaultValueConfigWorksheet worksheet)
        {
            if (filter == null)
                return "";
            //标识是否为首次加载有效字段条件，作为添加AND或OR关键字
            bool isFirst = true;
            StringBuilder where = new StringBuilder("");
            //处理字段查询明细
            if (filter.rules != null && filter.rules.Count > 0)
            {
                foreach (var item in filter.rules)
                {
                    bool ruleSearch = true;
                    if (!string.IsNullOrEmpty(item.field) && !string.IsNullOrEmpty(item.op))
                    {
                        var fieldFind = field.FirstOrDefault(x => x.Key == item.field);
                        //处理值,若是动态值
                        if (item.opType == "2")
                        {
                            //如果是关联字段
                            if (fieldFind.Type == EnumZeroCodeControl.correlation.ToString())
                            {
                                //转换值
                                var data = values.FirstOrDefault(f => f.Key == item.data);
                                if (data != null)
                                {
                                    item.data = data.Value == null ? null : data.Value.ToString().JsonStringToList<string>().ExpandAndToString();
                                }
                                //判断类型，是否忽略值为空情况
                                if (item.data.IsNullOrEmpty())
                                {
                                    if (worksheet.DynamicValueEmpty == 4)
                                    {
                                        item.op = "nu";//直接查询空值
                                    }
                                    //否则直接不查询当前条件
                                    else
                                    {
                                        ruleSearch = false;
                                    }
                                }
                            }
                        }
                        if (ruleSearch)
                        {
                            if (isFirst != true)
                            {
                                //非首个条件添加AND或者OR
                                where.AppendFormat(" {0} ", filter.groupOp);
                            }
                            where.Append(ConvertFilters(item, field));
                            isFirst = false;
                        }
                    }
                }
            }

            //处理嵌套查询
            if (filter.groups != null && filter.groups.Count > 0)
            {
                foreach (var item in filter.groups)
                {
                    string child = ConvertFiltersWorkSheet(item, field, values, worksheet);
                    if (!string.IsNullOrEmpty(child))
                    {
                        if (isFirst != true)
                        {
                            //非首个条件添加AND或者OR
                            where.AppendFormat(" {0} ", filter.groupOp);
                        }
                        where.Append(child);
                        isFirst = false;
                    }
                }
            }

            //返回
            return where.Length > 0 ? $" ({where}) " : "";
        }

        /// <summary>
        /// 处理单个字段查询，匹配数据类型及查询方式
        /// </summary>
        /// <param name="rule">查询字段对象</param>
        /// <returns></returns>
        private static string ConvertFilters(FilterRules rule, List<SystemWorkSheetTableFieldDetailInput> field)
        {
            string phoneKey = "Ph376A@e^270Ks_~";
            if (string.IsNullOrEmpty(rule.op) || string.IsNullOrEmpty(rule.field))
            {
                return "";
            }
            var fieldFind = field.FirstOrDefault(x => x.Key == rule.field);
            //Sql注入替换后数据
            string data = rule.data.FilterSql();
            StringBuilder searchCase = new StringBuilder();
            switch (rule.op)
            {
                case "eq": //等于
                    if (fieldFind.Type == EnumZeroCodeControl.correlation.ToString())
                    {
                        searchCase.Append(($" ( JSON_CONTAINS({fieldFind.Key}, '\"{rule.data}\"', '$')>0 and JSON_Length({fieldFind.Key})=1 )"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " ='" + data + "'"));
                    }
                    break;
                case "eqphone": //等于
                    searchCase.Append(GetFilter(rule.field, " ='" + SearchFilterUtil.Encrypt(data, phoneKey) + "'"));
                    break;
                case "ne": //不等于
                    if (fieldFind.Type == EnumZeroCodeControl.correlation.ToString())
                    {
                        searchCase.Append(($"  JSON_CONTAINS({fieldFind.Key}, '\"{rule.data}\"', '$')=0"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " !='" + data + "'"));
                    }

                    break;
                case "bw": //以...开始
                    searchCase.Append(GetFilter(rule.field, " like '" + data + "%'"));
                    break;
                case "bn": //不以...开始
                    searchCase.Append(GetFilter(rule.field, " not like '" + data + "%'"));
                    break;
                case "ew": //结束于
                    searchCase.Append(GetFilter(rule.field, " like '%" + data + "'"));
                    break;
                case "en": //不结束于
                    searchCase.Append(GetFilter(rule.field, " not like '%" + data + "'"));
                    break;
                case "lt": //小于
                    searchCase.Append(GetFilter(rule.field, " <'" + data + "'"));
                    break;
                case "le": //小于等于
                    searchCase.Append(GetFilter(rule.field, " <='" + data + "'"));
                    break;
                case "gt": //大于
                    searchCase.Append(GetFilter(rule.field, " >'" + data + "'"));
                    break;
                case "ge": //大于等于
                    searchCase.Append(GetFilter(rule.field, " >='" + data + "'"));
                    break;
                case "in": //包括
                    searchCase.Append(GetFilter(rule.field, " in ('" + data + "')"));
                    break;
                case "ni": //不包含
                    searchCase.Append(GetFilter(rule.field, " not in ('" + data + "')"));
                    break;
                case "cn"://包含
                    searchCase.Append(GetFilter(rule.field, " like '%" + data + "%' "));
                    break;
                case "nc"://不包含
                    searchCase.Append(GetFilter(rule.field, " not like '%" + data + "%'"));
                    break;
                case "nu"://空值
                    if (fieldFind.Type == EnumZeroCodeControl.correlation.ToString())
                    {
                        searchCase.Append((" (" + rule.field + " is null or JSON_LENGTH(" + rule.field + ")=0) "));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " is null"));
                    }
                    break;
                case "nn"://非空值
                    if (fieldFind.Type == EnumZeroCodeControl.correlation.ToString())
                    {
                        searchCase.Append((" (" + rule.field + " is not null and JSON_LENGTH(" + rule.field + ")>0) "));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " is not null"));
                    }
                    break;
                case "dateeq"://时间等于
                    if (data.Length == 4)//年
                    {
                        searchCase.Append(GetFilter(rule.field, " between '" + data + "-01-01 00:00:00' AND '" + data + "-12-31 23:59:59'"));
                    }
                    else if (data.Length == 7) //年月
                    {
                        string endTime = Convert.ToDateTime(data).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
                        searchCase.Append(GetFilter(rule.field, " between '" + data + "-01 00:00:00' AND '" + endTime + " 23:59:59'"));
                    }
                    else if (data.Length == 10) //年月日
                    {
                        searchCase.Append(GetFilter(rule.field, " between '" + data + " 00:00:00' AND '" + data + " 23:59:59'"));
                    }
                    else if (data.Length == 13) //年月日时
                    {
                        searchCase.Append(GetFilter(rule.field, " between '" + data + " :00:00' AND '" + data + " :59:59'"));
                    }
                    else if (data.Length == 16) //年月日时分
                    {
                        searchCase.Append(GetFilter(rule.field, " between '" + data + " :00' AND '" + data + " :59'"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " ='" + data + "'"));
                    }
                    break;
                case "datelt"://小于
                    if (data.Length == 4)//年
                    {
                        searchCase.Append(GetFilter(rule.field, " <'" + data + "-01-01 00:00:00'"));
                    }
                    else if (data.Length == 7) //年月
                    {
                        searchCase.Append(GetFilter(rule.field, " <'" + data + "-01 00:00:00' '"));
                    }
                    else if (data.Length == 10) //年月日
                    {
                        searchCase.Append(GetFilter(rule.field, " <'" + data + " 00:00:00'"));
                    }
                    else if (data.Length == 13) //年月日时
                    {
                        searchCase.Append(GetFilter(rule.field, " <'" + data + " :00:00'"));
                    }
                    else if (data.Length == 16) //年月日时分
                    {
                        searchCase.Append(GetFilter(rule.field, " <'" + data + " :00'"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " <'" + data + "'"));
                    }
                    break;
                case "datele"://小于等于
                    if (data.Length == 4)//年
                    {
                        searchCase.Append(GetFilter(rule.field, " <='" + data + "-01-01 00:00:00'"));
                    }
                    else if (data.Length == 7) //年月
                    {
                        searchCase.Append(GetFilter(rule.field, " <='" + data + "-01 00:00:00' '"));
                    }
                    else if (data.Length == 10) //年月日
                    {
                        searchCase.Append(GetFilter(rule.field, " <='" + data + " 00:00:00'"));
                    }
                    else if (data.Length == 13) //年月日时
                    {
                        searchCase.Append(GetFilter(rule.field, " <='" + data + " :00:00'"));
                    }
                    else if (data.Length == 16) //年月日时分
                    {
                        searchCase.Append(GetFilter(rule.field, " <='" + data + " :00'"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " <='" + data + "'"));
                    }
                    break;
                case "dategt"://大于
                    if (data.Length == 4)//年
                    {
                        searchCase.Append(GetFilter(rule.field, " >'" + data + "-01-01 00:00:00'"));
                    }
                    else if (data.Length == 7) //年月
                    {
                        searchCase.Append(GetFilter(rule.field, " >'" + data + "-01 00:00:00' '"));
                    }
                    else if (data.Length == 10) //年月日
                    {
                        searchCase.Append(GetFilter(rule.field, " >'" + data + " 00:00:00'"));
                    }
                    else if (data.Length == 13) //年月日时
                    {
                        searchCase.Append(GetFilter(rule.field, " >'" + data + " :00:00'"));
                    }
                    else if (data.Length == 16) //年月日时分
                    {
                        searchCase.Append(GetFilter(rule.field, " >'" + data + " :00'"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " >'" + data + "'"));
                    }
                    break;
                case "datege"://大于等于
                    if (data.Length == 4)//年
                    {
                        searchCase.Append(GetFilter(rule.field, " >='" + data + "-01-01 00:00:00'"));
                    }
                    else if (data.Length == 7) //年月
                    {
                        searchCase.Append(GetFilter(rule.field, " >='" + data + "-01 00:00:00' '"));
                    }
                    else if (data.Length == 10) //年月日
                    {
                        searchCase.Append(GetFilter(rule.field, " >='" + data + " 00:00:00'"));
                    }
                    else if (data.Length == 13) //年月日时
                    {
                        searchCase.Append(GetFilter(rule.field, " >='" + data + " :00:00'"));
                    }
                    else if (data.Length == 16) //年月日时分
                    {
                        searchCase.Append(GetFilter(rule.field, " >='" + data + " :00'"));
                    }
                    else
                    {
                        searchCase.Append(GetFilter(rule.field, " >='" + data + "'"));
                    }
                    break;
                case "daterange"://针对时间特别处理
                    var dateTime = data.Split('|');
                    if (dateTime.Length == 2)
                    {
                        string beginTime = dateTime[0];
                        string endTime = dateTime[1];
                        if (beginTime.Length == 4) //年
                        {
                            beginTime = beginTime + "-01-01 00:00:00";
                            endTime = endTime + "-01-01 00:00:00";
                        }
                        else if (beginTime.Length == 7) //年月
                        {
                            beginTime = beginTime + "-01 00:00:00";
                            endTime = endTime + "-01 00:00:00";
                        }
                        else if (beginTime.Length == 10) //年月日
                        {
                            beginTime = beginTime + "00:00:00";
                            endTime = endTime + "00:00:00";
                        }
                        searchCase.Append(GetFilter(rule.field, " between '" + beginTime + "' AND '" + endTime + "'"));
                    }
                    break;
                case "jcn"://包含
                    searchCase.Append($"JSON_CONTAINS({rule.field}, '{{\"value\": \"{data}\"}}', '$')");
                    break;
            }
            return searchCase.ToString();
        }

        /// <summary>
        /// 转换
        /// </summary>
        /// <param name="field"></param>
        /// <param name="formula"></param>
        /// <returns></returns>
        private static string GetFilter(string field, string formula)
        {
            string search = string.Empty;
            StringBuilder searchCase = new StringBuilder();
            var split = field.Split(',');
            if (split.Length > 1)
            {
                foreach (var fi in field.Split(','))
                {
                    search += fi + formula + " OR " /* " like '%" + data + "%' OR "*/;
                }
                searchCase.Append("(" + search.Substring(0, search.Length - 4) + ")");
            }
            else
            {
                searchCase.Append(field + formula);
            }
            return searchCase.ToString();
        }

        #endregion
    }
}